Snowflake Lead / Lag Recipes


Time Elapsed between two events.

Let’s say you need to calculate the number of days between two events and the compare the metrics associated with those two events. We will take the example of LSAT Tests and the Test scores. We want to see the how long the students wait till they re-take the test and determine if the scores improve or not.

Let’s first create some sample data:

CREATE OR REPLACE TABLE SCRATCH.TEST.LSAT_SCORE (LSAC_ID number, STUDENT_NAME varchar(40), TEST_DATE DATE, TEST_SCORE number);

INSERT INTO SCRATCH.TEST.LSAT_SCORE VALUES (1, 'Groucho Marx', to_date('1910-12-01'), 135);
INSERT INTO SCRATCH.TEST.LSAT_SCORE VALUES (2, 'Harpo Marx', to_date('1910-12-15'), 128);
INSERT INTO SCRATCH.TEST.LSAT_SCORE VALUES (1, 'Groucho Marx', to_date('1911-10-01'), 138);
INSERT INTO SCRATCH.TEST.LSAT_SCORE VALUES (2, 'Harpo Marx', to_date('1911-02-15'), 128);

Next let’s use the Lag function to determined number of days between the test re-take and the score difference associated with re-take.

SELECT *,
LAG(TEST_DATE) over (PARTITION by LSAC_ID order by TEST_DATE) as PREVIOUS_TEST_DATE,
DATEDIFF("day", PREVIOUS_TEST_DATE, TEST_DATE) as TEST_DAY_APART,
LAG(TEST_SCORE) over (PARTITION by LSAC_ID order by TEST_SCORE) as PREVIOUS_TEST_SCORE,
TEST_SCORE-PREVIOUS_TEST_SCORE AS SCORE_IMPROVEMENT
FROM SCRATCH.TEST.LSAT_SCORE

Here is the output of the Lag Query. You can see the number of days elapsed since the last take of the test and the score improvement (if any).

LSAC_IDSTUDENT_NAMETEST_DATETEST_SCOREPREVIOUS_TEST_DATETEST_DAY_APARTPREVIOUS_TEST_SCORESCORE_IMPROVEMENT
2Harpo Marx1910-12-15128    
2Harpo Marx1911-02-151281910-12-15621280
1Groucho Marx1910-12-01135    
1Groucho Marx1911-10-011381910-12-013041353