Practice SQL analytics vocabulary: window functions, CTEs, pivot and unpivot, materialized views, and talking about query performance optimization.
0 / 5 completed
1 / 5
What does 'RANK() OVER (PARTITION BY region ORDER BY revenue DESC)' do?
This is a window function. PARTITION BY divides the result set into groups (here by region). ORDER BY sets the ranking order. RANK() assigns a rank within each partition. Result: each region has its own ranking sequence — top revenue row in each region gets rank 1.
2 / 5
What is a CTE (Common Table Expression) and why is it useful?
A CTE (WITH clause) defines a named temporary query result. Instead of nested subqueries, you write: WITH active_users AS (SELECT ...), then reference 'active_users' like a table. CTEs improve readability, enable recursion, and can be referenced multiple times in the same query.
3 / 5
What does it mean to 'pivot' data in SQL analytics?
A pivot transforms row values into column headers. For example, a sales table with rows for each month becomes a table with columns Jan, Feb, Mar... This is common for reporting dashboards where you want to compare across periods in a single row.
4 / 5
What is a materialized view and how does it differ from a regular view?
A regular view is a saved query — it re-executes every time you query it. A materialized view pre-computes and stores the results physically. This dramatically speeds up complex analytical queries that would otherwise take minutes to run on raw data.
5 / 5
A colleague says 'this query took 45 seconds — let's optimize it.' What are the first things you would check?
Query optimization starts with: 1) EXPLAIN / EXPLAIN ANALYZE to see the execution plan, 2) missing indexes on filtered/joined columns, 3) eliminating full table scans, 4) pre-aggregating in a materialized view if the query runs frequently, 5) partitioning large tables by date or key columns.