[go: up one dir, main page]
Scribd
Upload
24 views3 pages

SQL Practice QA

The document contains SQL queries for various data retrieval tasks, including fetching employee names with department names, calculating total purchase amounts per customer, ranking student scores, detecting consecutive numbers in logs, and identifying sales statistics. It also includes queries for finding top customers and products by revenue, as well as analyzing login streaks and purchase patterns. Each query is designed to extract specific insights from a database using SQL syntax.

Uploaded by

Sfcs Priyansh Malviya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views3 pages

SQL Practice QA

The document contains SQL queries for various data retrieval tasks, including fetching employee names with department names, calculating total purchase amounts per customer, ranking student scores, detecting consecutive numbers in logs, and identifying sales statistics. It also includes queries for finding top customers and products by revenue, as well as analyzing login streaks and purchase patterns. Each query is designed to extract specific insights from a database using SQL syntax.

Uploaded by

Sfcs Priyansh Malviya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Q1. Get employee names with their department names.

Solution:
SELECT e.name, d.dept_name
FROM Employees e
JOIN Departments d ON e.department_id = d.id;

Q2. Find total purchase amount per customer (order by total spent).

Solution:
SELECT customer_id, SUM(amount) AS total_spent
FROM Orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Q3. Rank student scores (highest first).

Solution:
SELECT score,
DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM Scores;

Q4. Detect consecutive numbers in Logs (lead function).

Solution:
SELECT num
FROM (
SELECT num,
LEAD(num) OVER() AS next1,
LEAD(num, 2) OVER() AS next2
FROM Logs
)t
WHERE num = next1 AND num = next2;

Q5. Find highest sales per seller.

Solution:
SELECT seller_id, MAX(amount) AS highest_sales
FROM Sales
GROUP BY seller_id;

Q6. Find 2nd highest sale per seller.

Solution:
SELECT seller_id, amount AS second_highest_sale
FROM (
SELECT seller_id, amount,
ROW_NUMBER() OVER (PARTITION BY seller_id ORDER BY amount DESC) AS rn
FROM Sales
)t
WHERE rn = 2;

Q7. Find 3-day consecutive login streaks.

Solution:
SELECT DISTINCT user_id
FROM (
SELECT user_id, activity_date,
LEAD(activity_date, 1) OVER (PARTITION BY user_id ORDER BY activity_date) AS next1,
LEAD(activity_date, 2) OVER (PARTITION BY user_id ORDER BY activity_date) AS next2
FROM Activity
)t
WHERE DATEDIFF(next1, activity_date) = 1
AND DATEDIFF(next2, activity_date) = 2;

Q8. Find top customer by total amount spent.

Solution:
SELECT customer_id, name, total_spent
FROM (
SELECT c.customer_id, c.name, SUM(o.amount) AS total_spent
FROM Customer c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
)t
ORDER BY total_spent DESC
LIMIT 1;

Q9. Find top product by revenue in each category.

Solution:
WITH cte AS (
SELECT p.category_id, p.product_id, p.product_name,
SUM(o.quantity * o.price) AS revenue,
RANK() OVER (PARTITION BY p.category_id ORDER BY SUM(o.quantity * o.price) DESC) AS
rnk
FROM Products p
JOIN Orders o ON p.product_id = o.product_id
GROUP BY p.category_id, p.product_id, p.product_name
)
SELECT category_id, product_id, product_name, revenue
FROM cte
WHERE rnk = 1;
Q10. Longest streak of consecutive purchases per customer (Amazon-style).

Solution:
WITH ordered AS (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM Orders
),
grouped AS (
SELECT customer_id,
DATE_SUB(order_date, INTERVAL rn DAY) AS grp,
COUNT(*) AS streak_len
FROM ordered
GROUP BY customer_id, DATE_SUB(order_date, INTERVAL rn DAY)
)
SELECT customer_id, MAX(streak_len) AS longest_streak
FROM grouped
GROUP BY customer_id;

You might also like