Tags
#php
#kotlin
#bash
#go
#sql
#rust
#typescript
#html
#java
#python
#files
#utils
#strings
#http
#concurrency
#async
#json
#arrays
#security
#types
#crypto
#database
#dates
#format
SQL
ROW_NUMBER OVER PARTITION
Number rows within a partition — the most common "give me the Nth thing per group" pattern. Pair with a WHERE rn = 1 in an outer query to keep just the top row per group.
@admin
SQL
Running Totals (SUM OVER)
Window aggregates with an implicit "rows up to here" frame give you running totals, cumulative counts, and ratchet-up metrics. No self-join needed.
SQL
Cohort Retention Analysis
Group users by their signup week, then count how many were still active in each subsequent week. The classic SaaS retention table — entirely in SQL.
SQL
Sessionization — Group Events into Sessions
Stitch a stream of events into "sessions" where events more than N minutes apart start a new session. Uses LAG + a SUM-OVER trick to assign session IDs.
SQL
Moving Average (Window Frame)
Add a frame clause (`ROWS BETWEEN N PRECEDING AND CURRENT ROW`) to compute moving averages, sliding sums, rolling stats — anything that needs a bounded look-back.
SQL
RANK / DENSE_RANK
`RANK` leaves gaps after ties (1, 2, 2, 4); `DENSE_RANK` doesn't (1, 2, 2, 3). `ROW_NUMBER` is always unique. Pick by what tied rows should produce.
SQL
NTILE — Bucket Rows into N Buckets
Assign rows to N approximately-equal-sized buckets based on the ORDER BY. The standard way to compute quartiles, deciles, or any percentile bucket.
SQL
Gap Detection — Missing Days / Sequences
Find holes in a series — missing invoice numbers, days with no events, gaps in a sequence. Combine `LAG` (or `generate_series`) with a join to spot them.
SQL
FIRST_VALUE / LAST_VALUE
Return the first or last value within a window. Useful for "compare each row to the partition's first/last value" — for example, "how much have we grown since the user's first order?"