Aggregation & windows in the SQL assessment

What it covers

GROUP BY and HAVING, aggregate functions, and window functions such as ROW_NUMBER, RANK, running totals and comparisons with the previous row.

Topics in the practice set for this area

• aggregation
• window functions

How it is tested

In the timed assessment, questions in this area use these formats: code you write, multiple choice, short answer. Each attempt draws its own set of questions, and this area gets its own sub-score on the credential when the attempt includes enough questions from it. The assessment questions themselves are never published; the practice set below is written separately for study.

What to practice

• Solve top-N-per-group with a window function and again with a correlated subquery.
• Compute a running total and a seven-day moving average over a dated table.
• Know the difference between WHERE and HAVING well enough to explain it in one sentence.

Try a practice question

From the free practice set (4 questions in this area). Answers and explanations are in the practice set.

• Which SQL clause keeps only the groups whose aggregate meets a condition, such as COUNT(*) > 5?
• `SELECT COUNT(*) FROM users WHERE email IS NULL` returns:
• `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)` returns:

More SQL study guides

• How to Pass the Provyn SQL Assessment
• Query construction
• Joins & set logic
• Performance & indexing
• Transactions & correctness