-- 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;