--Dataset: Social Media Analytics table 'social_media', 'streams'
--Source: Analyse Data with SQL Skill Path Course, Codecademy

--Display the running total and the running average of followers for the 'instagram' user
SELECT 
   month,
   change_in_followers,
   ROUND(SUM(change_in_followers) OVER (
      ORDER BY month
   ), 2) AS 'running_total',
   ROUND(AVG(change_in_followers) OVER (
      ORDER BY month
   ), 2) AS 'running_avg'
FROM
   social_media
WHERE
   username = 'instagram';

--Display the running total and the running average of followers for all recorded accounts
SELECT 
    username,
    month,
    change_in_followers,
    ROUND(SUM(change_in_followers) OVER (
      PARTITION BY username 
      ORDER BY month
    ), 2) AS 'running_total_followers_change',
    ROUND(AVG(change_in_followers) OVER (
      PARTITION BY username 
      ORDER BY month
    ), 2) AS 'running_average_followers_change'
FROM
    social_media;

--Display the least number of posts recorded for a given month, per user, alongside their monthly number of posts
SELECT
   username,
   posts,
   FIRST_VALUE (posts) OVER (
      PARTITION BY username 
      ORDER BY posts
   ) fewest_posts
FROM
   social_media;

--Display the most number of posts recorded for a given month, per user, alongside their monthly number of posts
SELECT
   username,
   posts,
   LAST_VALUE (posts) OVER (
      PARTITION BY username 
      ORDER BY posts
      RANGE BETWEEN UNBOUNDED PRECEDING AND 
      UNBOUNDED FOLLOWING
    ) most_posts
FROM social_media;

--Display Lady Gaga's monthly change in number of streams and monthly change in chart position. Use LAG() functions
SELECT
   artist,
   week,
   streams_millions,
   ROUND(streams_millions - LAG(streams_millions, 1, streams_millions) OVER (
      PARTITION BY artist
      ORDER BY week
   ), 2) AS 'streams_millions_change',
   chart_position,
   LAG(chart_position, 1, chart_position) OVER ( 
    PARTITION BY artist
    ORDER BY week 
) - chart_position AS chart_position_change
FROM
   streams 
WHERE
   artist = 'Lady Gaga';

--Alter the previous query to calculate the same new columns but now for all artists and using LEAD() functions
SELECT
   artist,
   week,
   streams_millions,
  ROUND((LEAD(streams_millions, 1) OVER (
      PARTITION BY artist
      ORDER BY week
   ) - streams_millions), 2) AS 'streams_millions_change',
   chart_position,
   chart_position - LEAD(chart_position, 1) OVER (
    PARTITION BY artist
    ORDER BY week
   ) AS 'chart_position_change'
FROM
   streams;

--Use window functions to add row numbers to an ordered list of weekly streams per artist. Number 1 in the list should be the artist (and week) with the least streams
SELECT 
   ROW_NUMBER() OVER (
      ORDER BY streams_millions
   ) AS 'row_num', 
   artist, 
   week,
   streams_millions
FROM
   streams;

--Alter the previous query to display rank instead of row number. Tied numbers of streams should display the same rank
SELECT 
   RANK() OVER (
      ORDER BY streams_millions
   ) AS 'rank', 
   artist, 
   week,
   streams_millions
FROM
   streams;

--Alter the previous query to display the ranked list of artists partitioned by week so we can see the most streamed artist of each week
SELECT 
   RANK() OVER (
      PARTITION BY week
      ORDER BY streams_millions DESC
   ) AS 'rank', 
   artist, 
   week,
   streams_millions
FROM
   streams;

--Break the data into quintiles according to their number of streams per week
SELECT 
   NTILE(5) OVER (
      ORDER BY streams_millions DESC
   ) AS 'quintile', 
   artist, 
   week,
   streams_millions
FROM
   streams;

--Break the data into quartiles such that we now have quartiles for each week according to the number of streams per week i.e. each quartile contains two artists because we have eight artists total for each week in the dataset
SELECT 
   NTILE(4) OVER (
      PARTITION BY week
      ORDER BY streams_millions DESC
   ) AS 'quartile', 
   artist, 
   week,
   streams_millions
FROM
   streams;