Scribd
Upload
21 views1 page

SQL Interview QA

The document contains SQL interview questions and their solutions, focusing on customer spending, consecutive logins, recent orders, and post engagement scores. It includes specific SQL queries using common functions like CTEs, DENSE_RANK, LEAD, and ROW_NUMBER. Each question targets a different aspect of data analysis in a database context.

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
21 views1 page

SQL Interview QA

The document contains SQL interview questions and their solutions, focusing on customer spending, consecutive logins, recent orders, and post engagement scores. It includes specific SQL queries using common functions like CTEs, DENSE_RANK, LEAD, and ROW_NUMBER. Each question targets a different aspect of data analysis in a database context.

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

SQL Interview Questions & Solutions

Q1. Find the top 3 customers who spent the most in the last 30 days.
WITH cte AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM Transactions
WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM (
SELECT customer_id, total_spent,
DENSE_RANK() OVER (ORDER BY total_spent DESC) AS rnk
FROM cte
) t
WHERE rnk <= 3;

Q2. Find customers who logged in for 3 consecutive days.


WITH cte AS (
SELECT user_id, login_date,
LEAD(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS next1,
LEAD(login_date, 2) OVER (PARTITION BY user_id ORDER BY login_date) AS next2
FROM Logins
)
SELECT user_id
FROM cte
WHERE DATEDIFF(next1, login_date) = 1
AND DATEDIFF(next2, login_date) = 2;

Q3. Find the most recent order per customer.


WITH cte AS (
SELECT order_id, customer_id, amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rnk
FROM Orders
)
SELECT order_id, customer_id, amount
FROM cte
WHERE rnk = 1;

Q4. Find the top 5 posts by engagement score (Meta/Facebook).


SELECT post_id, user_id,
(likes * 2 + shares * 3) AS engagement_score
FROM Posts
WHERE post_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
ORDER BY engagement_score DESC
LIMIT 5;

You might also like