32 items
Running Total With Tied Dates
Code QuizLeak-Free Running Average Feature
QuizWindow Functions for Feature Engineering
FlashcardWindow Functions for Feature Engineering
Slides / VideoRolling Feature Data Leakage Bug
Code QuizLeak-Free Rolling Features
QuizWindow Functions for Feature Engineering
FlashcardWindow Functions Inside CTEs
QuizWindows for Feature Engineering
Slides / VideoTop Earner Per Department Bug
Code QuizFiltering on a Window Function Result
Code QuizFiltering on Window Function Results
QuizWindow Functions With CTEs
Slides / VideoFiltering on Window Function Results
FlashcardPercent-of-total With a Window Frame Bug
Code QuizNesting Aggregates Inside Window Functions
QuizCohort Retention Trend Bug
Code QuizFiltering On Window Function Results
QuizRunning Total With Duplicate Dates
Code QuizRunning Totals and Default Frames
QuizCohort & Trend Analysis With Windows
Slides / VideoRANGE vs ROWS Default Frame Peers
QuizDefault Frame: RANGE vs ROWS Gotcha
FlashcardWindow Functions & Advanced Aggregation
Slides / VideoAssigning Cohorts With Window Functions
QuizWindow Functions for Cohort & Trend Analysis
FlashcardJoins, Window Functions & CTEs
Slides / VideoFiltering on Window Function Results
QuizCombining CTEs, Joins & Window Functions
FlashcardWindow Functions & Query Performance
Slides / VideoWindow Functions and Query Performance
QuizWindow Functions & Query Performance
FlashcardCohort Retention Trend Bug
Spot the window-function mistake in a monthly cohort retention trend query.
WITH monthly AS (
SELECT cohort_month,
activity_month,
COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY cohort_month, activity_month
)
SELECT cohort_month,
activity_month,
active_users,
ROUND(
100.0 * active_users /
FIRST_VALUE(active_users) OVER (
ORDER BY activity_month
), 2) AS retention_pct
FROM monthly
ORDER BY cohort_month, activity_month;What is the bug in this retention-percentage calculation?