SQL Challenges for Data Analytics – With Explanation

SQL Challenges for Data Analytics – With Explanation ​

(Beginner Advanced)

---

Select Specific Columns

SELECT name, email FROM users;

This fetches only the name and email columns from the users table.
Used when you don’t want all columns from a table.

---

Filter Records with WHERE

SELECT * FROM users WHERE age > 30;

The WHERE clause filters rows where age is greater than 30.
Used for applying conditions on data.

---

ORDER BY Clause

SELECT * FROM users ORDER BY registered_at DESC;

Sorts all users based on registered_at in descending order.
Helpful to get latest data first.

---

Aggregate Functions (COUNT, AVG)

SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users;

Explanation:
• COUNT(*) counts total rows (users)
• AVG(age) calculates the average age
Used for quick stats from tables.

---

GROUP BY Usage

SELECT city, COUNT(*) AS user_count FROM users GROUP BY city;

Groups data by city and counts users in each group.
Use when you want grouped summaries.

---

JOIN Tables

SELECT users.name, orders.amount 
FROM users 
JOIN orders ON users.id = orders.user_id;

Fetches user names along with order amounts by joining users and orders on matching IDs.
Essential when combining data from multiple tables.

---

Use of HAVING

SELECT city, COUNT(*) AS total 
FROM users 
GROUP BY city 
HAVING COUNT(*) > 5;

Like WHERE, but used with aggregates.
Use HAVING after GROUP BY.

---

Subqueries

SELECT * FROM users 
WHERE salary > (SELECT AVG(salary) FROM users);

Finds users whose salary is above the average.
Nested queries for dynamic filtering.

---

CASE Statement

SELECT name, 
  CASE 
    WHEN age < 18 THEN 'Teen' 
    WHEN age <= 40 THEN 'Adult' 
    ELSE 'Senior' 
  END AS age_group 
FROM users;

Adds a new column that classifies users into categories based on age.
Powerful for conditional logic.

---

Window Functions (Advanced)

SELECT name, city, score, 
  RANK() OVER (PARTITION BY city ORDER BY score DESC) AS rank 
FROM users;

Ranks users by score within each city.
Great for leaderboards, dense ranking, and comparisons.

---