task_list.sql

--EDA
SELECT * FROM playlist;

SELECT DISTINCT album_type from playlist;

SELECT duration_min FROM playlist ORDER BY duration_min ASC ;

SELECT * FROM playlist WHERE duration_min=0;

DELETE FROM playlist WHERE duration_min=0;

SELECT DISTINCT channel from playlist;

SELECT DISTINCT most_played_on  from playlist;


--Task 

--Task 1:Retrieve the names of all tracks that have more than 1 billion streams.
SELECT * FROM playlist WHERE stream > 1000000000;

--Task 2:List all albums along with their respective artists.
SELECT DISTINCT album,artist FROM playlist ORDER BY 1;

--Task 3:Get the total number of comments for tracks where licensed = TRUE.
SELECT SUM(comments) as total_comments 
FROM playlist WHERE licensed='true';

--Task 4:Find all tracks that belong to the album type single.
SELECT track,album_type
FROM playlist WHERE album_type='single';

--Task 5:Count the total number of tracks by each artist.
SELECT artist,count(track) as total_tracks
FROM playlist
GROUP BY artist
ORDER BY 2 ; 

--Task 6:Calculate the average danceability of tracks in each album.
SELECT AVG(danceability) as Avg_danceability,track 
FROM playlist GROUP BY track
ORDER BY 2;

--Task 7:Find the top 10 tracks with the highest energy values.
SELECT MAX(energy) AS Energy_value,track FROM playlist
GROUP BY 2
ORDER BY 1 DESC
LIMIT 10 ;

--Task 8:List all tracks along with their total views and likes where official_video = TRUE.
SELECT track,
SUM(views) AS total_views,
SUM(likes) AS total_likes
FROM playlist WHERE official_video='true'
GROUP BY track
ORDER BY 2 DESC;

--Task 9:For each album, calculate the total views of all associated tracks.
SELECT SUM(views) AS total_views,album
FROM playlist
GROUP BY album
ORDER BY 1 DESC;

--Task 10:Retrieve the track names that have been streamed on Spotify more than YouTube.
SELECT track,
stream as spotify_stream,
views as youtube_views
FROM playlist
WHERE stream > views;

--Task 11:Find the top 3 most-viewed tracks for each artist using window functions.
WITH rank_track as(
SELECT track,artist,views,DENSE_RANK()OVER(PARTITION BY artist
ORDER BY views DESC)AS rn
from playlist)
SELECT * FROM rank_track WHERE rn<=3; 

--Task 12:Write a query to find tracks where the liveness score is above the average.
WITH liveness_track AS(
SELECT track,liveness,AVG(liveness)OVER() AS avg_liveness 
FROM playlist)
SELECT * FROM liveness_track  WHERE liveness> avg_liveness;

--Task 13:Use a WITH clause to calculate the difference between the highest and lowest energy values for tracks in each album.
WITH energy_track AS(
SELECT album,MAX(energy) AS highest_energy,
MIN(energy) AS lowest_energy
FROM playlist
GROUP BY album)
SELECT album,highest_energy-lowest_energy AS energy_diff
FROM energy_track
ORDER BY 2 DESC;

--Task 14:Find tracks where the energy-to-liveness ratio is greater than 1.2 .
SELECT track,energy,liveness,(energy/NULLIF(liveness,0)) AS energy_liveness_ratio FROM playlist
WHERE energy/NULLIF(liveness,0) > 1.2;

--Task 15:Calculate the cumulative sum of likes for tracks ordered by the number of views, using window functions.
WITH aggregated_tracks AS (
    SELECT 
        track,
        SUM(views) AS total_views,
        SUM(likes) AS total_likes
    FROM playlist
    GROUP BY track
),
cumulative_tracks AS (
    SELECT 
        track,
        total_views,
        total_likes,
        SUM(total_likes) OVER(ORDER BY total_views DESC) AS running_total_likes
    FROM aggregated_tracks
)
SELECT * FROM cumulative_tracks;


--Query Optimization
EXPLAIN ANALYZE  
WITH aggregated_tracks AS (
    SELECT 
        track,
        SUM(views) AS total_views,
        SUM(likes) AS total_likes
    FROM playlist
    GROUP BY track
),
cumulative_tracks AS (
    SELECT 
        track,
        total_views,
        total_likes,
        SUM(total_likes) OVER(ORDER BY total_views DESC) AS running_total_likes
    FROM aggregated_tracks
)
SELECT * FROM cumulative_tracks;

--Created Index
CREATE INDEX artist_index ON playlist(artist);