issue_comments: 970766486

https://covid-19.datasettes.com/covid is 805.2MB

https://covid-19.datasettes.com/covid/ny_times_us_counties?_trace=1&_facet_size=3&_size=2

Equivalent SQL:

https://covid-19.datasettes.com/covid?sql=with+cte+as+%28%0D%0A++select+rowid%2C+date%2C+county%2C+state%2C+fips%2C+cases%2C+deaths%0D%0A++from+ny_times_us_counties%0D%0A%29%2C%0D%0Atruncated+as+%28%0D%0A++select+null+as+_facet%2C+null+as+facet_name%2C+null+as+facet_count%2C+rowid%2C+date%2C+county%2C+state%2C+fips%2C+cases%2C+deaths%0D%0A++from+cte+order+by+date+desc+limit+4%0D%0A%29%2C%0D%0Astate_facet+as+%28%0D%0A++select+%27state%27+as+_facet%2C+state+as+facet_name%2C+count%28%29+as+facet_count%2C%0D%0A++null%2C+null%2C+null%2C+null%2C+null%2C+null%2C+null%0D%0A++from+cte+group+by+facet_name+order+by+facet_count+desc+limit+3%0D%0A%29%2C%0D%0Afips_facet+as+%28%0D%0A++select+%27fips%27+as+_facet%2C+fips+as+facet_name%2C+count%28%29+as+facet_count%2C%0D%0A++null%2C+null%2C+null%2C+null%2C+null%2C+null%2C+null%0D%0A++from+cte+group+by+facet_name+order+by+facet_count+desc+limit+3%0D%0A%29%2C%0D%0Acounty_facet+as+%28%0D%0A++select+%27county%27+as+_facet%2C+county+as+facet_name%2C+count%28%29+as+facet_count%2C%0D%0A++null%2C+null%2C+null%2C+null%2C+null%2C+null%2C+null%0D%0A++from+cte+group+by+facet_name+order+by+facet_count+desc+limit+3%0D%0A%29%2C%0D%0Atotal_count+as+%28%0D%0A++select+%27COUNT%27+as+_facet%2C+%27%27+as+facet_name%2C+count%28%29+as+facet_count%2C%0D%0A++null%2C+null%2C+null%2C+null%2C+null%2C+null%2C+null%0D%0A++from+cte%0D%0A%29%0D%0Aselect++from+truncated%0D%0Aunion+all+select++from+state_facet%0D%0Aunion+all+select++from+fips_facet%0D%0Aunion+all+select++from+county_facet%0D%0Aunion+all+select+*+from+total_count

sql with cte as ( select rowid, date, county, state, fips, cases, deaths from ny_times_us_counties ), truncated as ( select null as _facet, null as facet_name, null as facet_count, rowid, date, county, state, fips, cases, deaths from cte order by date desc limit 4 ), state_facet as ( select 'state' as _facet, state as facet_name, count(*) as facet_count, null, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ), fips_facet as ( select 'fips' as _facet, fips as facet_name, count(*) as facet_count, null, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ), county_facet as ( select 'county' as _facet, county as facet_name, count(*) as facet_count, null, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ), total_count as ( select 'COUNT' as _facet, '' as facet_name, count(*) as facet_count, null, null, null, null, null, null, null from cte ) select * from truncated union all select * from state_facet union all select * from fips_facet union all select * from county_facet union all select * from total_count

_facet | facet_name | facet_count | rowid | date | county | state | fips | cases | deaths -- | -- | -- | -- | -- | -- | -- | -- | -- | --   |   |   | 1917344 | 2021-11-15 | Autauga | Alabama | 1001 | 10407 | 154   |   |   | 1917345 | 2021-11-15 | Baldwin | Alabama | 1003 | 37875 | 581   |   |   | 1917346 | 2021-11-15 | Barbour | Alabama | 1005 | 3648 | 79   |   |   | 1917347 | 2021-11-15 | Bibb | Alabama | 1007 | 4317 | 92 state | Texas | 148028 |   |   |   |   |   |   |   state | Georgia | 96249 |   |   |   |   |   |   |   state | Virginia | 79315 |   |   |   |   |   |   |   fips |   | 17580 |   |   |   |   |   |   |   fips | 53061 | 665 |   |   |   |   |   |   |   fips | 17031 | 662 |   |   |   |   |   |   |   county | Washington | 18666 |   |   |   |   |   |   |   county | Unknown | 15840 |   |   |   |   |   |   |   county | Jefferson | 15637 |   |   |   |   |   |   |   COUNT |   | 1920593 |   |   |   |   |   |   |