How do I efficiently query for records that share a year?
This should return all the ids that have at least one record for each year in your date range:
SELECT
  id
FROM
  &lt;your table&gt;
WHERE
  CAST(strftime(&quot;%Y&quot;, date) as decimal) BETWEEN &lt;min year&gt; AND &lt;max year&gt;
GROUP BY
  id
HAVING
  COUNT(DISTINCT strftime(&quot;%Y&quot;, date)) = &lt;max year&gt; - &lt;min year&gt; + 1
;