SQL Queries for Practice & Interviews
Uploaded by
SamyuckthaSQL Queries for Practice & Interviews
Uploaded by
SamyuckthaCommon questions
Powered by AIUse this query: WITH StageCounts AS (SELECT purchase_status, COUNT(DISTINCT user_id) AS users FROM Sales GROUP BY purchase_status) SELECT purchase_status, users, SUM(users) OVER (ORDER BY CASE WHEN purchase_status = 'viewed' THEN 1 WHEN purchase_status = 'added_to_cart' THEN 2 WHEN purchase_status = 'completed' THEN 3 END) AS cumulative_users FROM StageCounts ORDER BY CASE WHEN purchase_status = 'viewed' THEN 1 WHEN purchase_status = 'added_to_cart' THEN 2 WHEN purchase_status = 'completed' THEN 3 END. This provides user counts at each stage .
Use this approach: SELECT Category, Revenue, SUM(Revenue) OVER (PARTITION BY Category ORDER BY Revenue ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum FROM Sales. It computes a running total that resets on the detection of a new category .
You can find missing numbers using: WITH RECURSIVE Seq AS (SELECT 1 AS number UNION ALL SELECT number + 1 FROM Seq WHERE number < 100) SELECT number FROM Seq WHERE number NOT IN (SELECT number FROM Numbers) ORDER BY number. This generates a sequence from 1 to 100 and identifies which are missing .
A complex query like: SELECT product1, product2, COUNT(*) AS co_occurrences FROM (SELECT t1.product_id AS product1, t2.product_id AS product2 FROM Transactions t1 JOIN Transactions t2 ON t1.transaction_id = t2.transaction_id AND t1.product_id < t2.product_id) AS pairs GROUP BY product1, product2 ORDER BY co_occurrences DESC LIMIT 5 finds co-occurring pairs in transactions .
A common query method is: SELECT salary FROM (SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM Salary) AS ranked WHERE rnk = N. This assigns ranks to salaries and filters the result to find the Nth highest .
To achieve this, use: SELECT CASE WHEN age BETWEEN 18 AND 24 THEN '18-24' WHEN age BETWEEN 25 AND 34 THEN '25-34' WHEN age BETWEEN 35 AND 44 THEN '35-44' WHEN age BETWEEN 45 AND 54 THEN '45-54' ELSE '55+' END AS age_group, gender, COUNT(*) AS total_customers, AVG(purchase_amount) AS avg_purchase_amount FROM Customers GROUP BY age_group, gender. This query aggregates statistics by defined age ranges and gender .
First, calculate conversion rates with: SELECT group_name, SUM(CASE WHEN conversion_status = 'converted' THEN 1 ELSE 0 END) AS converted_users, COUNT(*) AS total_users, AVG(CASE WHEN conversion_status = 'converted' THEN 1.0 ELSE 0 END) AS conversion_rate FROM (SELECT 'ControlGroup' AS group_name, user_id, conversion_status FROM ControlGroup UNION ALL SELECT 'ExperimentalGroup' AS group_name, user_id, conversion_status FROM ExperimentalGroup) GROUP BY group_name. To determine statistical significance, further statistical testing will be required beyond just SQL .
Use the following SQL query: SELECT action, DATE(timestamp) AS date, COUNT(DISTINCT user_id) AS unique_users FROM UserActivity WHERE DATE(timestamp) = '2023-07-25' GROUP BY action. This query counts the distinct users grouped by action for the specified date .
Use the RANK() function: SELECT Student, Scores, RANK() OVER (ORDER BY Scores DESC) AS rank FROM Students. This function ensures that if two students have the same score, they share the same rank, and subsequent ranks are adjusted accordingly .
Use the formula calculating distance between coordinates: SELECT location_id, latitude, longitude, ((latitude - :latitude_target) * (latitude - :latitude_target) + (longitude - :longitude_target) * (longitude - :longitude_target)) AS distance FROM Locations ORDER BY distance ASC LIMIT 5. This selects the nearest locations based on calculated distances .