Aggregation, Windows, Subqueries & Table Variables

Which ROWS frame gives a 3-row moving average centered on the current row?

You find legacy code using CE_ functions (CE_PROJECTION, CE_JOIN...). Should you keep writing them?

You must run a table function once per input row and UNION the results — without an imperative loop. What operator?

What does next_task hold for each row? ``` LEAD(id) OVER ( PARTITION BY project ORDER BY id) AS next_task ```

How does MAP_REDUCE extend MAP_MERGE?

Why does SQLScript push you toward declarative code (SELECT only, no loops) instead of row-by-row logic?

A procedure is meant to be declarative. Adding what kind of statement quietly turns it imperative and blocks optimization?

In a SELECT's FROM clause, what's the difference between reading a database table, a table variable, and a table parameter?

Do you have to DECLARE a table variable before assigning to it in SQLScript?

What does this return with project? ``` SELECT project, SUM(effort) FROM tasks; ```

How do COUNT(*), COUNT(col), and COUNT(DISTINCT col) differ?

Column bonus holds (100, NULL, 200). What do SUM(bonus) and AVG(bonus) return?

You need each language's team names joined into one comma-separated string, one row per language. Which aggregate?

You need per-(status, assignee) counts AND per-assignee totals in ONE query. What do you use?

You move a status = 3 row filter into the HAVING clause to speed up an aggregation. Good idea?

In declarative SQLScript, should you add ORDER BY to a table variable's SELECT just to be safe?

Two SELECTs combined: when does UNION give a different result than UNION ALL?

INTERSECT vs an INNER JOIN + DISTINCT on the key — what's the real difference?

You run this straight in the SQL console: ``` lt = SELECT * FROM status; SELECT * FROM :lt; ``` What happens?

Inside a block, lt1 is a table variable: ``` lt2 = :lt1; ``` Does this copy it into lt2?

Why does this fail? ``` SELECT CASE ... END AS bucket, count(*) FROM tasks GROUP BY bucket; ```

Why is this sort risky? ``` SELECT * FROM tasks ORDER BY 7, 8; ```

SQLScript supports the WITH (CTE) clause. Why does the book still prefer table variables?

You join users twice (as assignee and as manager) and SELECT id from each. Why won't it compile without aliases?

How does COUNT(*) OVER (PARTITION BY status) differ from COUNT(*) ... GROUP BY status?

The subquery here returns 2 rows — and what if it returned 0? ``` WHERE status = ( SELECT id FROM status WHERE is_final) ```

How does adding ORDER BY change what this window COUNT sees? ``` COUNT(*) OVER ( PARTITION BY status ORDER BY id) ```