using a simple sql statement my results show several measurements that are worse than the answer of TULARE for that particular date. In fact TULARE was 47th on my list?
WHERE parameter=’o3′ AND date.utc>’2018-10-09’AND date.utc<‘2018-10-10′ AND country=’US’
ORDER BY value desc, date.utc DESC limit 100
If I remember correctly, the data is stored by data in the S3 bucket, so if you chose s3://openaq-fetches/realtime/2018-10-09/ as the source location for your air_quality table, you should not need to specify the date in the WHERE clause. I don’t do this in the VIEW creation.
If you’re selecting from the whole batch or more than one day, you might be pulling in some additional records. I also think your greater than / less than date clause might not be working as you’d expect. I think you need to CAST as Date. Check out this StackOverflow post:
You might also try BETWEEN…something like this: BETWEEN date ‘2014-07-05’ AND date ‘2014-08-05’