How do I efficiently query for records that share a year? I've got data that looks like
data
uid  id  date        value
1    a   1780-01-01  10
2    a   1780-01-02  11
3    a   1780-01-03  12
4    b   1780-01-01  10
...
999  a   1980-01-01  10

I'd like to know which ids have records between 1880 and 1900.  I currently
generate this big query (which I believe works):
SELECT year1880.id FROM
(SELECT DISTINCT id FROM data WHERE strftime(&quot;%Y&quot;, date) = &quot;1880&quot;) as year1880

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime(&quot;%Y&quot;, date) = &quot;1881&quot;) as year1881
  ON year1880.id = year1881.id

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime(&quot;%Y&quot;, date) = &quot;1882&quot;) as year1882
  ON year1880.id = year1882.id

  ...

  INNER JOIN
  (SELECT DISTINCT id FROM data WHERE strftime(&quot;%Y&quot;, date) = &quot;1900&quot;) as year1900
  ON year1880.id = year1900.id
  ;

Is this the most efficient way to make this query?  Could it be done in a closed
form so I don't need to generate a giant query with python?
The database is sqlite.
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
;