Key SQL Query Concepts for Interviews Quiz

Test your basic understanding of common SQL queries frequently discussed in interviews, including filtering, aggregation, and analytic functions.

1. Rolling Average Calculation

   Which SQL clause allows you to calculate a rolling 7-day average for sales amounts based on sale_date?

   1. ROLLING_AVG(sales_amount, 7) OVER sale_date
   2. AVG(sales_amount) PARTITION BY sale_date
   3. AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
   4. SUM(sales_amount) GROUP BY sale_date
   5. AVG(sales_amount) WHERE INTERVAL 7 DAY

2. Second-Highest Value

   How would you retrieve the second-highest salary in each department from an employees table?

   1. MIN(salary) WHERE department_id IN employees
   2. MAX(salary) WHERE salary u003C (SELECT MAX(salary) ... ) GROUP BY department_id
   3. SELECT department_id, salary FROM employees
   4. MAX(salary) WHERE department_id = salary
   5. SELECT salary WHERE salary = MAX(salary)

3. Filtering Times

   Which SQL WHERE clause would you use to select orders placed between 9:00 AM and 5:00 PM?

   1. WHERE order_time = '09:00:00' AND '17:00:00'
   2. WHERE CAST(order_time AS TIME) BETWEEN '09:00:00' AND '17:00:00'
   3. WHERE order_time u003E= '09:00' OR order_time u003C= '17:00'
   4. WHERE order_time LIKE '09:00:00-17:00:00%'
   5. WHERE order_time IN ('09:00:00', '17:00:00')

4. Identifying Missing Data

   If you need to find dates with no sales recorded for each product, which SQL operator should you use to check for missing records after a LEFT JOIN?

   1. WHERE s.sale_date IS NULL
   2. WHERE s.sale_date != NULL
   3. WHERE s.sale_date IS EMPTY
   4. WHERE s.sale_date = ''
   5. WHERE s.sale_date NULL

5. First Order in the Last Month

   To find customers who placed their first order in the last month, which clause should you include after a GROUP BY?

   1. HAVING MIN(order_date) u003E= DATEADD(month, -1, GETDATE())
   2. GROUP BY customer_id HAVING order_date u003E GETDATE()
   3. WHERE MIN(order_date) = LAST_MONTH()
   4. WHERE order_date BETWEEN LAST_MONTH() AND NOW()
   5. HAVING MAX(order_date) u003C GETDATE()

6. Multiple Products Per Order

   How can you retrieve customer orders containing more than one type of product?

   1. HAVING SUM(product_id) = 1
   2. ORDER BY COUNT(product_id)
   3. HAVING COUNT(DISTINCT product_id) u003E 1
   4. HAVING COUNT(product_id) u003E 1
   5. GROUP BY product_id HAVING customer_id u003E 1

7. Orders Above Average

   Which SQL expression identifies orders where the order amount is more than twice the average order amount?

   1. WHERE order_amount u003E 2 * (SELECT AVG(order_amount) FROM orders)
   2. WHERE order_amount u003E AVG(order_amount) * 2
   3. WHERE order_amount u003E 2 AVG(order_amount)
   4. WHERE AVG(order_amount) u003C order_amount * 2
   5. WHERE order_amount u003E= DOUBLE AVG(order_amount)

8. Average Time Between Orders

   Which function calculates the previous order date for each customer, enabling you to compute the average number of days between orders?

   1. PAST_DATE()
   2. ROW_NUMBER()
   3. PREVIOUS()
   4. LEAD()
   5. LAG()

9. Highest Revenue Month

   Which SQL function can help rank months by revenue within each year to find the highest revenue month per year?

   1. COUNT() OVER (ORDER BY year)
   2. RANK() OVER (PARTITION BY year ORDER BY revenue DESC)
   3. GROUP BY RANK()
   4. ORDER BY revenue AND year
   5. RANKING(year, month, revenue)

10. Grouping for Top Products

    To find the most popular product in each category based on sales, which clause should you use for grouping?

    1. GROUP BY category_id, product_name
    2. SELECT MAX(sales) BY category_id
    3. GROUP BY product_id
    4. PARTITION BY product_name
    5. GROUP category_id, sales