sidorenkov

Answer #5

Aug 3rd, 2021
880
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. drop TABLE if EXISTS track_downloads;
  2.  
  3. CREATE TABLE track_downloads  (
  4.     download_id  SERIAL,
  5.     track_id  INTEGER NOT NULL,
  6.     user_id INTEGER NOT NULL,
  7.     download_time timestamp without time zone NOT NULL
  8. );
  9.  
  10. insert into track_downloads(track_id, user_id, download_time) VALUES
  11. (1, 1, TO_TIMESTAMP('2010-11-19', 'YYYY-MM-DD')),
  12. (1, 2, TO_TIMESTAMP('2010-11-19', 'YYYY-MM-DD')),
  13. (2, 1, TO_TIMESTAMP('2010-11-19', 'YYYY-MM-DD')),
  14. (2, 2, TO_TIMESTAMP('2010-11-19', 'YYYY-MM-DD')),
  15. (2, 3, TO_TIMESTAMP('2010-11-19', 'YYYY-MM-DD')),
  16. (1, 1, TO_TIMESTAMP('2010-11-18', 'YYYY-MM-DD')),
  17. (1, 2, TO_TIMESTAMP('2010-11-18', 'YYYY-MM-DD')),
  18. (2, 1, TO_TIMESTAMP('2010-11-20', 'YYYY-MM-DD')),
  19. (2, 2, TO_TIMESTAMP('2010-11-20', 'YYYY-MM-DD')),
  20. (2, 3, TO_TIMESTAMP('2010-11-20', 'YYYY-MM-DD'));
  21.  
  22. SELECT count(user_id), download_count from(
  23.   select user_id, count(download_id) as download_count from track_downloads
  24.   where download_time = '2010-11-19'
  25.   group by user_id
  26. ) as t
  27. group by download_count;
  28.  
  29.  
  30.  
RAW Paste Data