Something went wrong. Try again.
This string has no description
Something went wrong. Try again.
2.7 kB · 66 lines · Created 9mo ago
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566WITH user_top_artists AS ( SELECT user_id, artist_id, COUNT(*) as play_count, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) as artist_rank FROM scrobbles s INNER JOIN artists a ON a.id = s.artist_id WHERE s.artist_id IS NOT NULL AND a.name != 'Various Artists' AND user_id IN ('rec_cug4h6ibhfbm7uq5dte0', 'rec_d3tv55lqrj614pp2mgag') GROUP BY user_id, artist_id),user_totals AS ( SELECT user_id, COUNT(DISTINCT artist_id) as total_artists FROM user_top_artists WHERE artist_rank <= 50 -- Use fewer artists for normalization GROUP BY user_id),shared_weighted AS ( SELECT u1.artist_id, u1.artist_rank as user1_rank, u2.artist_rank as user2_rank, (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) as artist_weight, ROW_NUMBER() OVER (ORDER BY (1.0 / u1.artist_rank) * (1.0 / u2.artist_rank) DESC) as weight_rank FROM user_top_artists u1 INNER JOIN user_top_artists u2 ON u1.artist_id = u2.artist_id AND u1.user_id = 'rec_cug4h6ibhfbm7uq5dte0' AND u2.user_id = 'rec_d3tv55lqrj614pp2mgag' WHERE u1.artist_rank <= 50 -- Consider fewer artists AND u2.artist_rank <= 50),compatibility_calc AS ( SELECT SUM(sw.artist_weight) as weighted_overlap, COUNT(*) as shared_count, (SELECT total_artists FROM user_totals WHERE user_id = 'rec_cug4h6ibhfbm7uq5dte0') as user1_total, (SELECT total_artists FROM user_totals WHERE user_id = 'rec_d3tv55lqrj614pp2mgag') as user2_total FROM shared_weighted sw)SELECT -- Simpler Jaccard-like formula (more generous) ROUND( (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100, 1 ) as compatibility_percentage, CASE WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 20 THEN 'Low' WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 40 THEN 'Medium' WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 60 THEN 'High' WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 75 THEN 'Very High' WHEN (shared_count * 1.0 / LEAST(user1_total, user2_total)) * 100 < 90 THEN 'Super' ELSE 'ZOMG!1!' END as compatibility_level, shared_count as shared_artists, user1_total as user1_artist_count, user2_total as user2_artist_count, LIST(a.name ORDER BY sw.artist_weight DESC) FILTER (WHERE sw.weight_rank <= 10) as top_shared_artistsFROM compatibility_calcCROSS JOIN shared_weighted swINNER JOIN artists a ON a.id = sw.artist_idGROUP BY weighted_overlap, shared_count, user1_total, user2_total;