SQL LeetCode Problems: A Beginner-Friendly Guide 🎯

Welcome to our SQL tutorial designed specifically for beginners and intermediates! In this lesson, we'll dive into solving SQL problems from LeetCode, a popular platform for coding practice. 💡 Note: This tutorial will cover both SQL syntax and problem-solving skills.

Introduction 📝

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It's essential for developers and data analysts to understand SQL to work with databases efficiently.

Getting Started 💡

Before we dive into the problems, let's cover the basics:

Tables: SQL stores data in tables, which are collections of rows and columns.
SELECT: The SELECT statement is used to retrieve data from tables.
WHERE: Filters the rows based on conditions.
JOIN: Combines rows from two or more tables based on a related column between them.

Problem 1: Simple SQL Query 📝

Problem: Write a SQL query to get the number of unique customers who made a purchase in the year 2021.

sql

-- Assuming we have a table named 'purchases' with columns 'customer_id' and 'purchase_date'

SELECT DISTINCT customer_id
FROM purchases
WHERE YEAR(purchase_date) = 2021;

Explanation:

DISTINCT keyword is used to return unique customer IDs.
YEAR() function retrieves the year from the 'purchase_date' column.
WHERE clause filters the rows based on the year being 2021.

Quick Quiz

Question 1 of 1

What does the DISTINCT keyword do in the above query?

Problem 2: SQL JOIN 💡

Problem: Write a SQL query to get the name and total amount spent by each customer in 2021.

sql

-- Assuming we have two tables: 'customers' with 'customer_id' and 'name' and 'purchases' with 'customer_id', 'purchase_amount', and 'purchase_date'

SELECT customers.name, SUM(purchases.purchase_amount) as total_spent
FROM customers
JOIN purchases ON customers.customer_id = purchases.customer_id
WHERE YEAR(purchases.purchase_date) = 2021
GROUP BY customers.customer_id, customers.name;

Explanation:

JOIN combines rows from two tables based on the 'customer_id' column.
SUM() aggregates the 'purchase_amount' column.
GROUP BY clause groups the results by customer ID and name.

Quick Quiz

Question 1 of 1

What is the purpose of the JOIN statement in the above query?

Remember, mastering SQL takes practice! Solving these types of problems will help you become more comfortable working with databases and SQL. Happy coding! 💡