Window Functions & GROUPING

Preview — 3 of 10 questions

What does the following query compute?

javascript

SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
  (region, product),
  (region),
  ()
);

AOnly totals per (region, product) pair

BIt is equivalent to ROLLUP(region, product)

CTotals per (region, product) and per product only

DTotals per (region, product), subtotals per region and per product, and a grand total

Three employees have salaries: Alice=90k, Bob=90k, Charlie=80k. What are their values for each function?

javascript

SELECT name, salary,
  RANK()        OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rnk,
  ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_num
FROM employees;

ARANK: 1,1,2 — DENSE_RANK: 1,1,2 — ROW_NUMBER: 1,1,2

BRANK: 1,1,3 — DENSE_RANK: 1,1,2 — ROW_NUMBER: 1,2,3

CRANK: 1,2,3 — DENSE_RANK: 1,1,2 — ROW_NUMBER: 1,2,3

DRANK: 1,1,2 — DENSE_RANK: 1,1,2 — ROW_NUMBER: 1,2,3

Which function retrieves the previous row's value in a window?

javascript

SELECT date, revenue,
  ??? OVER (ORDER BY date) AS prev_revenue
FROM daily_sales;

ALEAD(revenue)

BSHIFT(revenue, -1)

CPREV(revenue)

DLAG(revenue)

Answer all 10 questions (7 more), see explanations for every answer, and track your score.