SQL query challenge - find top frequent items in columns and summarize result to a pivot table -
i looking query following transformation.
basically want find top 3 frequent sell_country , top 3 frequent category, on per website, per day bases. (for example, website 1, date 6-5-2017, there 2*us, 1*jp , 1*uk sell_country, therefore top1_sell_country us, , jp , uk going top2_sell_country , top3_sell_country. same idea category column)
my current solution involves many subqueries, works, feel complicated. interested in how sql master in elegant way.
currently know how uses
i in 3 steps:
- group country , rank count
- group category , rank count
- blend results using conditional aggregate (which place values in necessary cells because result of
case
value , manynull
values,min()
outputs value)
like this:
with countries ( select *, row_number() on (partition website,date order count desc) ( select website ,date::date ,sell_country ,count(1) your_table group 1,2,3 ) ) ,categories ( select *, row_number() on (partition website,date order count desc) ( select website ,date::date ,category ,count(1) your_table group 1,2,3 ) ) select website ,date ,coalesce(min(case when t1.row_number=1 t1.sell_country end),'na') top1_sell_country ,coalesce(min(case when t1.row_number=2 t1.sell_country end),'na') top2_sell_country ,coalesce(min(case when t1.row_number=3 t1.sell_country end),'na') top3_sell_country ,coalesce(min(case when t2.row_number=1 t2.category end),'na') top1_sell_category ,coalesce(min(case when t2.row_number=2 t2.category end),'na') top2_sell_category ,coalesce(min(case when t2.row_number=3 t2.category end),'na') top3_sell_category countries t1 full join categories t2 using (website,date) group 1,2 order 1,2
Comments
Post a Comment