Facebook
From Buff Hornbill, 5 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 249
  1. select * from user_actions;
  2.  
  3. --1.
  4. select  extract(year from aggregate_date) "year", sum(ue.clicks) SumOfClicks, sum(ue.shows) SumOfShows from user_actions ue
  5. group by  extract(year from aggregate_date)
  6. order by "year";
  7.  
  8.  
  9. --2.
  10. select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
  11. group by  extract(year from aggregate_date)
  12. order by "year";
  13.  
  14. --3.
  15. select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
  16. where section='Sport'
  17. group by  extract(year from aggregate_date)
  18. order by "year";
  19.  
  20. --4.
  21. select  extract(year from aggregate_date) "year", sum(ue.clicks)/sum(ue.shows) CTR from user_actions ue
  22. where section='Sport'
  23. group by  extract(year from aggregate_date)
  24. order by "year" desc;