issue_comments: 970738130
This data as json
| html_url | issue_url | id | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| https://github.com/simonw/datasette/issues/1513#issuecomment-970738130 | https://api.github.com/repos/simonw/datasette/issues/1513 | 970738130 | IC_kwDOBm6k_c453EnS | 9599 | 2021-11-16T22:32:19Z | 2021-11-16T22:32:19Z | OWNER | I came up with the following query which seems to work! ```sql with cte as ( select rowid, country, country_long, name, owner, primary_fuel from [global-power-plants] ), truncated as ( select null as _facet, null as facet_name, null as facet_count, rowid, country, country_long, name, owner, primary_fuel from cte order by rowid limit 4 ), country_long_facet as ( select 'country_long' as _facet, country_long as facet_name, count(*) as facet_count, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ), owner_facet as ( select 'owner' as _facet, owner as facet_name, count(*) as facet_count, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ), primary_fuel_facet as ( select 'primary_fuel' as _facet, primary_fuel as facet_name, count(*) as facet_count, null, null, null, null, null, null from cte group by facet_name order by facet_count desc limit 3 ) select * from truncated union all select * from country_long_facet union all select * from owner_facet union all select * from primary_fuel_facet ``` (Limits should be 101, 31, 31, 31 but I reduced size to get a shorter example table). Results [look like this](https://global-power-plants.datasettes.com/global-power-plants?sql=with+cte+as+%28%0D%0A++select+rowid%2C+country%2C+country_long%2C+name%2C+owner%2C+primary_fuel%0D%0A++from+%5Bglobal-power-plants%5D%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+country%2C+country_long%2C+name%2C+owner%2C+primary_fuel%0D%0A++from+cte+order+by+rowid+limit+4%0D%0A%29%2C%0D%0Acountry_long_facet+as+%28%0D%0A++select+%27country_long%27+as+_facet%2C+country_long+as+facet_name%2C+count%28*%29+as+facet_count%2C%0D%0A++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%0Aowner_facet+as+%28%0D%0A++select+%27owner%27+as+_facet%2C+owner+as+facet_name%2C+count%28*%29+as+facet_count%2C%0D%0A++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%0Aprimary_fuel_facet+as+%28%0D%0A++select+%27primary_fuel%27+as+_facet%2C+primary_fuel+as+facet_name%2C+count%28*%29+as+facet_count%2C%0D%0A++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%0D%0Aselect+*+from+truncated%0D%0Aunion+all+select+*+from+country_long_facet%0D%0Aunion+all+select+*+from+owner_facet%0D%0Aunion+all+select+*+from+primary_fuel_facet): _facet | facet_name | facet_count | rowid | country | country_long | name | owner | primary_fuel -- | -- | -- | -- | -- | -- | -- | -- | -- | | | 1 | AFG | Afghanistan | Kajaki Hydroelectric Power Plant Afghanistan | | Hydro | | | 2 | AFG | Afghanistan | Kandahar DOG | | Solar | | | 3 | AFG | Afghanistan | Kandahar JOL | | Solar | | | 4 | AFG | Afghanistan | Mahipar Hydroelectric Power Plant Afghanistan | | Hydro country_long | United States of America | 8688 | | | | | | country_long | China | 4235 | | | | | | country_long | United Kingdom | 2603 | | | | | | owner | | 14112 | | | | | | owner | Lightsource Renewable Energy | 120 | | | | | | owner | Cypress Creek Renewables | 109 | | | | | | primary_fuel | Solar | 9662 | | | | | | primary_fuel | Hydro | 7155 | | | | | | primary_fuel | Wind | 5188 | | | | | | This is a neat proof of concept. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1055469073 |