Essential SQL Interview Concepts for Data Roles Quiz

Sharpen your understanding of common SQL interview questions focused on purchase data analysis, filtering, group aggregation, and date logic. Perfect for candidates targeting data science, analytics, and engineering roles who want to master key SQL concepts tested in technical interviews.

1. Filtering Purchases by Date

   Which SQL clause should be used to restrict the results to purchases made exactly during the last one month from today?

   1. HAVING purchase_date = CURRENT_DATE
   2. GROUP BY purchase_date
   3. ORDER BY purchase_date DESC
   4. WHERE purchase_date u003E CURRENT_DATE - INTERVAL '1 month'

   Explanation: The WHERE clause filters rows based on the purchase date to ensure only those made in the last month are considered. ORDER BY only sorts results, not filters them. GROUP BY is for aggregating data based on columns, not for filtering by date. HAVING is used to filter after aggregation, and the condition shown does not find purchases from the last month.

2. Identifying Successful Purchases

   A purchase is considered successful if it is not returned within a week. Which SQL condition accurately selects such purchases?

   1. WHERE return_date IS NOT NULL
   2. WHERE return_date u003C purchase_date + INTERVAL '7 days'
   3. WHERE purchase_date = return_date
   4. WHERE return_date IS NULL OR return_date u003E purchase_date + INTERVAL '7 days'

   Explanation: Successful purchases are those with no return or with a return date after one week, which is properly captured by the given condition. The second option only finds purchases returned quickly, not successful ones. The third only finds returned purchases, missing out those never returned. The last option checks for same-day purchase and return, which is incorrect.

3. Grouping Multiple Purchases

   To find customers with multiple successful purchases, which SQL clause is essential for counting the number of purchases per customer?

   1. LIMIT customer_id
   2. ORDER BY COUNT(*)
   3. JOIN customer_id
   4. GROUP BY customer_id

   Explanation: GROUP BY is used to aggregate purchases by customer so you can count how many each has. JOIN is for combining tables, not grouping. ORDER BY COUNT(*) just sorts but does not count or group. LIMIT restricts the number of returned rows and does not perform grouping.

4. Using Aggregate Functions in SQL

   Which SQL function helps identify customers with more than one successful purchase in a filtered dataset?

   1. MAX()
   2. COUNT(*)
   3. LEN()
   4. ROUND()

   Explanation: COUNT(*) tallies the number of purchases per customer, letting you filter for those with multiple. MAX() finds the largest value, which is not suitable here. ROUND() is a mathematical function unrelated to counting rows. LEN() measures string length and is not useful for counting purchases.

5. Filtering Groups Based on Aggregate Results

   After grouping by customer_id, which clause allows you to select only customers with more than one successful purchase?

   1. ORDER BY customer_id
   2. SELECT COUNT(*) u003E 1
   3. WHERE COUNT(*) u003E 1
   4. HAVING COUNT(*) u003E 1

   Explanation: HAVING is the correct clause for filtering groups based on aggregate results like COUNT(*). WHERE cannot use aggregated values directly. ORDER BY just changes the result order. SELECT COUNT(*) u003E 1 is not valid SQL syntax for filtering.

6. Checking for Missing Return Dates

   Which SQL operator checks if the return_date field is missing for a purchase?

   1. NOT NULL
   2. IS NULL
   3. LIKE NULL
   4. = NULL

   Explanation: IS NULL checks for missing or null values in a field, perfectly suited for this scenario. '= NULL' is invalid syntax and will not work correctly. 'NOT NULL' is a keyword for checking presence, not absence, and 'LIKE NULL' is not a valid operation.

7. Combining Multiple SQL Conditions

   When ensuring both date and successful purchase conditions, which keyword should be used to combine these in a WHERE clause?

   1. BY
   2. AND
   3. FROM
   4. OR

   Explanation: AND combines multiple conditions so both must be true for a row to be included, which suits filtering by purchase date and return criteria. OR allows either condition, which is not correct for this case. BY is used elsewhere, like GROUP BY, not in conditional expressions. FROM defines data sources, unrelated to condition logic.

8. Understanding Table Structure

   Given a table with purchase_id, customer_id, purchase_date, and return_date, which field is most suitable for identifying unique transactions?

   1. return_date
   2. purchase_date
   3. customer_id
   4. purchase_id

   Explanation: purchase_id uniquely identifies each transaction, making it ideal for this purpose. customer_id identifies individuals, not transactions. purchase_date and return_date may repeat for different transactions and are unsuitable as unique identifiers.

9. Selecting Current Date in SQL

   Which SQL function is commonly used to obtain today's date for filtering recent purchases?

   1. NEW_DATE
   2. GET_DATE()
   3. CURRENT_DATE
   4. DATE_NOW

   Explanation: CURRENT_DATE retrieves today's date in SQL, standard for date calculations. NEW_DATE and DATE_NOW are not standard SQL functions. GET_DATE() is used in some systems but not standard, so CURRENT_DATE is reliably cross-platform.

10. Interpreting a NULL Return Date

    If the return_date for a purchase is NULL, what does this indicate about the purchase status?

    1. The customer returned the item before purchase
    2. The item was returned the same day
    3. The purchase_id is missing
    4. The item was not returned

    Explanation: A NULL in the return_date means the item has not been returned. A same-day return would show an actual date value. purchase_id missing would occur in a different field, and an item returned before purchase is impossible and illogical.