24 lines
904 B
SQL
24 lines
904 B
SQL
-- sqlite3 -csv db.sqlite3 '.read export.sql' > export.csv
|
|
SELECT
|
|
CONCAT('https://open.spotify.com/track/', T.id) as spotify_id,
|
|
T.name as track_name,
|
|
A.name as artist_name,
|
|
(CAST(T.duration_ms AS REAL) / 86400000) as duration,
|
|
V.point_total,
|
|
V.vote_count,
|
|
CAST(V.point_total AS REAL) / CAST(V.vote_count AS REAL) as average_points,
|
|
U.username as nominated_by
|
|
FROM
|
|
(
|
|
SELECT track_id, SUM(points) as point_total, COUNT(*) as vote_count
|
|
FROM playlist_vote
|
|
GROUP BY track_id
|
|
) AS V
|
|
INNER JOIN playlist_track T ON V.track_id = T.id
|
|
INNER JOIN (
|
|
SELECT track_id, MIN(artist_id) as artist_id FROM playlist_track_artists GROUP BY track_id
|
|
) PTA ON T.id = PTA.track_id
|
|
INNER JOIN playlist_artist A ON PTA.artist_id = A.id
|
|
INNER JOIN auth_user U ON T.nominated_by_id = U.id
|
|
ORDER BY average_points DESC, vote_count DESC;
|