.output stdout
create table data
(
	date_id timestamp
		primary key,
	month int
)
;

create table song
(
	song_id varchar(18)
		primary key,
	artist varchar(256),
	title varchar(256)
)
;

create table fact_table
(
	track_id integer
		primary key
		 autoincrement,
	song_id varchar(18)
		constraint fact_table2_song_fk
			references song,
	user_id varchar(40),
	date_id timestamp
		constraint fact_table2_date_fk
			references data
)
;

create table playbacks
(
	user_id varchar(40),
	song_id varchar(18),
	date_id timestamp
)
;

create table tracks
(
	track_id varchar(18) not null
		primary key,
	song_id varchar(18) not null,
	artist varchar(256) default NULL,
	title varchar(256) default NULL
)
;

.separator ,
.import unique_tracks.txt tracks

.separator ,
.import triplets_sample_20p.txt playbacks

insert into fact_table(song_id, user_id, date_id)
    select playbacks.song_id, user_id, date_id
from playbacks left join tracks on playbacks.song_id = tracks.song_id;

insert into song
    select song_id, artist, title
from tracks group by song_id;

insert into data
  select date_id, strftime("%m", datetime(date_id, 'unixepoch'))
from playbacks group by date_id;

-- drop table tracks;
-- drop table playbacks;

.separator ' '
select title, artist, cnt
from (select song_id, count(*) as cnt from fact_table group by song_id order by count(song_id) desc limit 10) as rank
inner join song on rank.song_id=song.song_id;
-- You're The One	Dwight Yoakam	145267
-- Undo	Bj�rk	129778
-- Revelry	Kings Of Leon	105162
-- Sehr kosmisch	Harmonia	84981
-- Horn Concerto No. 4 in E flat K495: II. Romance (Andante cantabile)	Barry Tuckwell/Academy of St Martin-in-the-Fields/Sir Neville Marriner	77632
-- Dog Days Are Over (Radio Edit)	Florence + The Machine	71300
-- Secrets	OneRepublic	58472
-- Use Somebody	Kings Of Leon	58458
-- Canada	Five Iron Frenzy	54655
-- Invalid	Tub Ring	53494


select user_id, count(distinct song_id)
from fact_table group by user_id order by count(distinct song_id) desc limit 10;
-- ec6dfcf19485cb011e0b22637075037aae34cf26	1040
-- 119b7c88d58d0c6eb051365c103da5caf817bea6	641
-- b7c24f770be6b802805ac0e2106624a517643c17	637
-- 4e73d9e058d2b1f2dba9c1fe4a8f416f9f58364f	592
-- d7d2d888ae04d16e994d6964214a1de81392ee04	586
-- 6d625c6557df84b60d90426c0116138b617b9449	584
-- 113255a012b2affeab62607563d03fbdf31b08e7	561
-- c1255748c06ee3f6440c51c439446886c7807095	547
-- db6a78c78c9239aba33861dae7611a6893fb27d5	529
-- 99ac3d883681e21ea68071019dba828ce76fe94d	499


select artist, count(*)
from fact_table join song s on fact_table.song_id = s.song_id
group by artist order by count(*) desc limit 1;
-- Kings Of Leon	230846


select month, count(*)
from fact_table join data d on fact_table.date_id = d.date_id
group by month order by month asc;
-- 1	2412154
-- 2	2195895
-- 3	2413551
-- 4	2337779
-- 5	2417027
-- 6	2334905
-- 7	2412210
-- 8	2413409
-- 9	2337837
-- 10	2413621
-- 11	2335127
-- 12	2396553


select user_id
from fact_table where song_id in (select f.song_id
from (select * from fact_table group by song_id order by count(*) desc limit 2000) f inner join song
where f.song_id = song.song_id and song.artist = 'Queen'
group by f.song_id
order by count(f.song_id)
desc limit 3)
group by user_id
having count(distinct fact_table.song_id) >= 3
order by user_id
limit 10;
-- ebae91ba3fa0f74003113db635b8733401e78eef
-- d0bbd36f1d5543af0d08347d919811094d1c1357
-- 8dd090f036cdb6940b4d18b79387db6789e6deda
-- 67e48469cdb146b0abbc0810d82cf0a6160030a6
-- 85ed8bc5bc1a6c4cd06efaa6f73f06ae1f4e101f
-- a4b4ee9767ccab25adae784b1d6cff75fc3a133b
-- 00832bf55ed890afeb2b163024fbcfaf58803098
-- 215e9dd4935737010daff9e7d014d530cc1eab41
-- 3456be91da73360b9197a554a8e07754720fb044
-- 7f378eb82733fe2975efa4acf82f94a87ac6f8c6