Apa iku window functions?

Question

IT Interview Practice · Accepted Answer

**Window functions** nindakake pitungan ing satengah kombinasi baris ("window") **sing ana hubungane karo baris saiki**, *tanpa nyambung dadi siji baris output* (beda karo aggregates karo `GROUP BY`). Padha ngidini analitik sing kuwat — total sing mlaku, ranking, perbandingan menyang baris liyane — nalika tetep saben baris ing asil.

## Beda kunci saka GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Ranking functions

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

`OVER (...)` klausul nentokake window. **`PARTITION BY`** mbagi baris dadi grup (kaya GROUP BY, nanging per-baris), lan **`ORDER BY`** nata ing saben partisi. `ROW_NUMBER`/`RANK`/`DENSE_RANK` atur ranking.

## Aggregate window functions (total sing mlaku, agregat per-grup)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Agregates minangka window functions ngetung total sing mlaku, rata-rata bergerak, utawa agregat per-partisi nalika tetep saben baris — ora bisa karo GROUP BY.

## LAG/LEAD — bandha menyang baris liyane

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** akses nilai saka baris sadurunge/sabanjure — sempurna kanggo perbandingan periode-over-periode (pertumbuhan bulan-over-bulan, etc.).

## Mengapa iku penting

Window functions minangka fitur SQL sing kuwat lan maju penting kanggo **analytical queries**, lan paham babagan mau iku ilmu sing berharga level senior kanggo analisis data, reporting, lan querying sing kompleks.

Kapabilitas sing ndefineake — nindakake pitungan ing baris sing ana hubungane **nalika tetep saben baris** (beda karo GROUP BY, sing nyambung baris) — ngidini kelas analitik kabeh: **rankings** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — panemuan top N per grup, atur ranking), **total sing mlaku lan rata-rata bergerak** (jumlah kumulatif, agregat per-partisi katon ing saben baris), lan **perbandingan baris-kanggo-baris** (`LAG`/`LEAD` kanggo analisis periode-over-periode kaya pertumbuhan bulan-over-bulan).

Mau kebutuhan analitik sing umum, berharga (leaderboard, metrik kumulatif, analisis trend, bandha saben baris menyang rata-rata grup) sing window functions tangani kanthi elegan ing siji query, dene SQL sadurunge window function butuh self-join kompleks utawa subquery.

Paham `OVER (PARTITION BY ... ORDER BY ...)` syntax (ndefineake window), ranking functions, aggregate window functions (total sing mlaku, agregat per-partisi), lan `LAG`/`LEAD` (akses baris liyane) nutupi toolkit analitik sing kuwat.

Saliyane analisis data lan reporting canggih minangka pangguna SQL sing penting, lan saliyane window functions minangka cara modern, kuwat kanggo nyatakake ranking, pitungan mlaku, lan perbandingan baris (kapabilitas lan elegan luwih gede tinimbang alternatif), masterat window functions minangka ilmu sing berharga, asring diterapake kanggo SQL analitik sing mbedakake developer sing bisa nulis query data canggih — tandha SQL skill advanced lan topik umum ing role lan interview sing data-fokus.

Beda kunci saka GROUP BY

Ranking functions

Aggregate window functions (total sing mlaku, agregat per-grup)

LAG/LEAD — bandha menyang baris liyane

Mengapa iku penting

Pitakon sing gegandhengan