90% of Candidates Get Confused: Easy But Tricky SQL Questions Quiz

1. Find Top N Salary Earners by City and Department

   Given a table containing employee details with columns for employee, salary, city, and department, which SQL window function would you typically use to assign a unique row number within each city and department ordered by salary descending? For example, generating row numbers to pick the top three salary earners in each group.

   1. ROW_NUMBER() OVER (PARTITION BY city, department ORDER BY salary DESC)
   2. COUNT(*) GROUP BY city, department
   3. MAX(salary) OVER (ORDER BY city, department)
   4. SUM(salary) PARTITION BY department
   5. ROW_COUNT() IN city, department

2. Moving Average Calculation

   If you need to calculate a moving average of the last 5 order amounts in a sales table, which window function would be most appropriate to use in combination with the AVG() function?

   1. ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
   2. GROUP BY 5 ROWS
   3. DISTINCT ON LAST 5 ROWS
   4. LIMIT 5 PER ORDER
   5. ORDER BY DESC, TOP 5

3. Percentile Calculation in SQL

   Suppose you want to calculate the 25th and 75th percentile of a column named 'score' in your table. Which SQL function would be the best choice to use for this purpose?

   1. PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score)
   2. RANK() OVER (ORDER BY score ASC)
   3. AVG(score) GROUP BY 0.25
   4. DENSERANK(score, 0.25)
   5. ORDER BY score LIMIT 25 PERCENT

4. Difference Between RANK() and DENSE_RANK()

   In SQL window functions, what is the main difference between RANK() and DENSE_RANK() when used in a query?

   1. RANK() leaves gaps in ranking for ties, while DENSE_RANK() does not
   2. DENSE_RANK() assigns the highest value to the lowest item
   3. RANK() sorts alphabetically; DENSE_RANK() sorts numerically
   4. DENSE_RANK() requires an extra ORDER BY clause
   5. RANK() can only be used with GROUP BY

5. Calculating Incremental Values from Cumulative Data

   Given a table with a 'cumulative_sales' column ordered by date, what window function should you use to calculate daily incremental sales (i.e., today's value minus yesterday's), for example, to track daily growth?

   1. LAG(cumulative_sales, 1) OVER (ORDER BY date)
   2. RANK() OVER (ORDER BY cumulative_sales)
   3. MIN(cumulative_sales) GROUP BY date
   4. SUM(cumulative_sales) PARTITION BY date
   5. LEAD_SUM(cumulative_sales, 1) GROUP BY date