Scribd
Upload
4 views4 pages

Faang SQL Practice

The document provides a collection of SQL practice questions and solutions, focusing on various topics such as joins, aggregations, window functions, and common table expressions (CTEs). It includes queries for tasks like finding the highest sales, consecutive login streaks, and top spenders. The content is tailored for FAANG-level SQL practice, showcasing essential SQL techniques and functions.

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
4 views4 pages

Faang SQL Practice

The document provides a collection of SQL practice questions and solutions, focusing on various topics such as joins, aggregations, window functions, and common table expressions (CTEs). It includes queries for tasks like finding the highest sales, consecutive login streaks, and top spenders. The content is tailored for FAANG-level SQL practice, showcasing essential SQL techniques and functions.

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/ 4

Priyansh Malviya - FAANG SQL Practice Questions & Solutions

1. Employees and Departments Join

Query:

SELECT e.name, d.dept_name


FROM employees e
JOIN Departments d ON e.department_id = d.id;

2. Customer Total Amount

Query:

SELECT customer_id, SUM(amount) as total_amount


FROM Orders
GROUP BY customer_id
ORDER BY total_amount DESC;

3. Scores Dense Rank

Query:

SELECT score, DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) AS


rank
FROM Scores;

4. Lead Example for Logs

Query:

SELECT LEAD(num, 1) OVER (ORDER BY id) AS next_num


FROM Logs;

1
5. Highest Sales per Seller

Query:

SELECT seller_id, MAX(amount) AS highest_sales


FROM Sales
GROUP BY seller_id;

6. Second Highest Sale per Seller

Query:

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;

7. Consecutive Login Streak

Query:

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;

8. Top Spender per Customer (Last 30 Days)

Query:

2
WITH cte AS (
SELECT user_id, SUM(amount) AS total_spent,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM Transactions
WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY user_id
)
SELECT user_id, total_spent
FROM cte
WHERE rank <= 3;

9. Longest Order Streak per Customer

Query:

WITH streaks AS (
SELECT customer_id, order_date,
order_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY order_date) DAY AS grp
FROM Orders
)
SELECT customer_id, COUNT(*) AS longest_streak
FROM streaks
GROUP BY customer_id, grp
ORDER BY customer_id;

10. Top Spender in Last 90 Days

Query:

WITH cte AS (
SELECT customer_id, SUM(amount) AS total_spent,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM Orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
)
SELECT customer_id, total_spent
FROM cte
WHERE rnk = 1;

3
11. Second Highest Salary per Department

Query:

WITH cte AS (
SELECT name, salary, department_id,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
AS rnk
FROM Employees
)
SELECT e.name, e.salary, d.dept_name
FROM cte e
JOIN Departments d ON e.department_id = d.id
WHERE rnk = 1;

This PDF contains all SQL questions with their solutions we discussed so far, including FAANG-level
practice, window functions, ranking, lead/lag, CTEs.

You might also like