-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.sql
33 lines (23 loc) · 1.12 KB
/
queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
select * from history limit 1000;
select count(artist) from history where artist = 'The Smiths'
select artist from history where artist = null
select artist, count(artist) from history where artist = 'Jane''s Addiction' group by artist
select track, count(track) from history where track = 'Stop' group by track
-- count by artist
select artist, count(artist) as total
from history group by artist order by total desc;
select dense_rank() over (order by count(artist) desc) as ranking, artist, count(artist) as total
from history group by artist order by total desc;
-- total unique artists
select count(distinct artist) from history;
select * from artist_ranking where artist = 'The Cure'
select max(ranking) as total_rank from artist_ranking
select count(*) from history where track = 'Stop';
select track, count(track) from history group by track order by count desc;
select count(*) from history where album = 'Wish';
select album, count(album) from history group by album order by count desc;
-- stats query
select artist, count(artist) as total
from history
where utc_time like '%Jul 2022%'
group by artist order by total desc;