Window Functions Assessment | SQL Window Functions

1

You need to show each product's revenue AND its category's total revenue in the same row. Which approach is correct?

SELECT revenue, SUM(revenue) FROM products GROUP BY category SELECT revenue, SUM(revenue) OVER (PARTITION BY category) FROM products SELECT revenue, (SELECT SUM(revenue) FROM products) FROM products SELECT revenue, SUM(revenue) OVER () FROM products

2

To find users who placed orders on consecutive days, which function helps?

ROW_NUMBER() RANK() LAG() NTILE()

3

Your sales team has 100 members. You want to identify the top 10% performers. Which function is most direct?

ROW_NUMBER() with WHERE <= 10 RANK() with WHERE <= 10 NTILE(10) with WHERE = 1 PERCENT_RANK() with WHERE <= 0.10

4

For a 30-day rolling sum of transactions per customer, which frame clause is correct?

ROWS BETWEEN 30 PRECEDING AND CURRENT ROW ROWS BETWEEN 29 PRECEDING AND CURRENT ROW RANGE BETWEEN 30 PRECEDING AND CURRENT ROW ROWS BETWEEN 1 PRECEDING AND 30 FOLLOWING

5

RANK() gives 1,1,3 for three salespeople, but you want 1,1,2. Which function should you use?

ROW_NUMBER() DENSE_RANK() NTILE() PERCENT_RANK()

6

This query fails: 'SELECT name, salary, AVG(salary) OVER () as avg_sal WHERE salary > avg_sal'. Why?

You can't use window functions with WHERE Window functions are computed after WHERE, so avg_sal doesn't exist in WHERE AVG requires GROUP BY The syntax for OVER is wrong

7

To get the first purchase date for each customer while keeping all their orders visible, use:

MIN(purchase_date) GROUP BY customer_id FIRST_VALUE(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) LAG(purchase_date, 1) OVER (PARTITION BY customer_id ORDER BY purchase_date) ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) = 1

8

What's wrong with: LAST_VALUE(price) OVER (PARTITION BY category ORDER BY date)

LAST_VALUE doesn't exist Missing PARTITION BY Default frame only includes rows up to current, not the actual last row ORDER BY should be DESC

9

Calculate month-over-month growth rate: (current - previous) / previous * 100. Which expression is correct?

100 * (revenue - LAG(revenue)) / LAG(revenue) OVER (ORDER BY month) 100 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) 100 * (revenue - LEAD(revenue) OVER (ORDER BY month)) / revenue 100 * (revenue - FIRST_VALUE(revenue) OVER (ORDER BY month)) / revenue

10

You're paginating results: page 5 with 20 items per page. Which ROW_NUMBER range do you need?

WHERE row_num BETWEEN 80 AND 100 WHERE row_num BETWEEN 81 AND 100 WHERE row_num BETWEEN 100 AND 120 WHERE row_num BETWEEN 101 AND 120

11

Which is more efficient for 'top 3 per category' on a million-row table?

Correlated subquery with LIMIT 3 ROW_NUMBER() OVER (PARTITION BY category) with WHERE rn <= 3 Multiple queries, one per category They're all equivalent

12

SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is equivalent to:

SUM(amount) OVER (ORDER BY date) SUM(amount) OVER () SUM(amount) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) Both A and C

13

Final question: Match the use case to the best window function. 'Remove duplicate entries keeping the most recent' uses:

RANK() with WHERE rank = 1 ROW_NUMBER() with WHERE row_num = 1 DENSE_RANK() with WHERE dense_rank = 1 Any of the above work equally well

Launch Offer