Skip to content

Instantly share code, notes, and snippets.

@YiLi225

YiLi225/running_total.sql

Last active March 9, 2020 15:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save YiLi225/1465a10c4416fea7e3970de91480b6fb to your computer and use it in GitHub Desktop.
Save YiLi225/1465a10c4416fea7e3970de91480b6fb to your computer and use it in GitHub Desktop.
Download ZIP
Raw
running_total.sql
--- 2) Running total/frequency
SELECT
DAT.NUM_VAR,
SUM(NUM_VAR) OVER (PARTITION BY JOIN_ID) AS TOTAL_SUM,
ROUND(CUM_SUM / SUM(NUM_VAR) OVER (PARTITION BY JOIN_ID), 4) AS CUM_FREQ
FROM
(
SELECT
T.*,
SUM(NUM_VAR) OVER (ORDER BY NUM_VAR ROWS UNBOUNDED PRECEDING) AS CUM_SUM,
CASE WHEN ID_VAR IS NOT NULL THEN '1' END AS JOIN_ID
FROM CURRENT_TABLE T
) DAT
ORDER BY CUM_FREQ
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment