viral_finder_sql.sql

WITH calculate_diff AS (
   SELECT
        post_id,
    num_shares,
data_scraped,
--max(data_scraped) maxdata,
    lag(num_shares) over (partition BY post_id ORDER BY data_scraped) as diff_1h,
    lag(num_shares,4) over (partition BY post_id ORDER BY data_scraped) as diff_4h,
    lag(num_shares,24) over (partition BY post_id ORDER BY data_scraped) as diff_1_day
    FROM  post_stats)
SELECT post_id, num_shares,
(num_shares - diff_1h) AS abs_change,
data_scraped,
--maxdata,
      COALESCE(round((100 * (num_shares - diff_1h)) / NULLIF(diff_1h,0), 2),0) AS percent_change,
	COALESCE(round((100 * (num_shares - diff_4h)) / NULLIF(diff_4h,0), 2),0) AS percent_change_4h,
	COALESCE(round((100 * (num_shares - diff_1_day)) / NULLIF(diff_1_day,0), 2),0) AS percent_change_1_day
FROM   calculate_diff
WHERE data_scraped = (SELECT MAX(data_scraped) FROM post_stats max_date WHERE calculate_diff.post_id = max_date.post_id) 
--WHERE data_scraped = (select max(data_scraped) from post_stats),
ORDER  BY percent_change_4h desc;