Navigating the early stages of a data analytics career sucks.
As an entry-level data analyst, I found myself staring at the screen, praying that I did well enough to get the job.
Interviewer watching me. Breathing down my neck. Clock ticking. The pressure to show off my SQL expertise was absolutely crushing. Any minute now, I knew the interviewer was going to stop the session and tell me to get the hell out of the room. Pissed at me for wasting his time.
That damned blinking cursor.
Doubts began to creep in.
- What if my SQL knowledge isn’t up to the mark?
- What if I miss a key piece of information in a data set?
- What if they see that I’m a total imposter and have no right to be there?
My confidence was shaken, and the fear of making a mistake loomed large. I was sinking into the quicksand of self-doubt, ready to abandon my career aspirations.
But then I stumbled upon a game-changer: practicing SQL problems of varying complexity – from beginner to advanced.
The mindset shift that reshaped my career trajectory.
I spent hours each day, working through SQL exercises.
The beginner ones felt comforting, reminding me of the basics I had mastered. The intermediate and advanced problems, however, pushed my limits, forcing me to think and solve more complex scenarios. Yes, you actually have to practice SQL if you want a data analytics job.
And with each passing day, something magical began to happen.
The lines of SQL code that used to intimidate me started to make sense. I could weave my way through complex databases and extract valuable insights more efficiently. The fear that once held me hostage began to fade away.
Suddenly, interviews were no longer a battleground but a platform to showcase my proficiency.
Imagine walking into an interview, confidently knowing you can tackle any SQL problem thrown your way. No more last-minute cramming, no more restless nights before an interview, and definitely no more self-doubt. This also helped as I was entering the data job market again down the road.
Now, you can build a fulfilling data analytics career with a solid foundation of SQL expertise.
A career where you are trusted with business-critical decisions, where your input shapes the company’s strategies, and where you can finally flex your data-crunching muscles without fear.
How do you make this happen, you ask?
It’s simple – by regularly practicing SQL problems.
To get you started, I’ve compiled a guide with beginner, intermediate, and advanced SQL problems.
It helped me navigate the stormy seas of the early data analytics career, and I’m confident it can help you too. Take the plunge, and let the journey to a successful, fear-free data analytics career begin. You’ve got this!
How this guide works:
- Use the database diagram below to write the SQL that answers each question
- The questions get more difficult and are based on real-world business problems.
- If you get stuck, all of the SQL code you need can be found here.
- I’ve also listed all of the answers below. But try it on your own first!
Use this database diagram to answer the questions.
 |
Questions
Question 1: Beginner Level
Can you write a SQL query to find the total sales revenue for a specific product?
Imagine you have a ‘sales’ table with columns ‘product_id’, ‘quantity’, and ‘price_per_unit’, and a ‘product’ table with ‘product_id’ and ‘product_name’.
Your task is to calculate the total revenue from the sales of a specific product.
The total revenue can be calculated by multiplying ‘quantity’ with ‘price_per_unit’ for each sale and then adding up the results for the selected product.
Try it on your own and then check your answers against the online SQL code found here.
Question 2: Intermediate Level
Can you write a SQL query to identify the top 5 customers who’ve bought the most number of products?
Let’s say you have a ‘sales’ table that includes columns ‘customer_id’ and ‘product_id’, and a ‘customer’ table with ‘customer_id’ and ‘customer_name’.
Your task is to write a SQL query that groups sales by customer, counts the number of distinct products bought by each customer, and then orders the customers in descending order of the number of products bought.
Finally, you’ll need to limit the output to the top 5 customers.
Try it on your own and then check your answers against the online SQL code found here.
Question 3: Advanced Level
Can you write a SQL query to forecast next month’s sales based on the previous month’s sales growth rate?
In this scenario, you have a ‘sales’ table with ‘sale_date’, ‘product_id’, ‘quantity’, and ‘price_per_unit’.
The challenge here is to calculate the month-over-month sales growth rate, and then use that to predict next month’s sales. This involves using window functions to compare sales in each month to the previous month, and date functions to extract the month and year from ‘sale_date’.
How would you do this in SQL?
Try it on your own and then check your answers against the online SQL code found here.
Answers
Answer 1: Total revenue from a specific product (Beginner)
Here’s my SQL. View the online version here.
-- Query 1: Total revenue from a specific product SELECT product_name, SUM(s.quantity * s.price_per_unit) AS total_revenue FROM sales s JOIN product p ON s.product_id = p.product_id WHERE p.product_name = 'Hitchhiker Umbrella' GROUP BY product_name;
- This query multiplies the ‘quantity’ sold and the ‘price_per_unit’ for each row in the ‘sales’ table and then adds up those amounts to find the total revenue.
- Join the ‘sales’ table with the ‘product’ table to match the product names on the product id.
- We’re filtering to only include sales of ‘Hitchhiker Umbrella’.
- Be careful with your joins. Make sure you’re joining on the correct columns.
Answer 1 Results
| product_name | total_revenue |
|---|---|
| Hitchhiker Umbrella | 6904.55 |
Answer 2: Top 5 customers who bought the most number of different products (Intermediate)
Here’s my SQL. View the online version here.
-- Query 2: Top 5 customers who bought the most number of different products SELECT c.customer_name, COUNT(DISTINCT s.product_id) AS number_of_products FROM sales s JOIN customer c ON s.customer_id = c.customer_id GROUP BY c.customer_name ORDER BY number_of_products DESC LIMIT 5;
- This query groups the sales by customer, counts the distinct number of products each customer bought, and orders the customers by the count of distinct products in descending order.
- COUNT(DISTINCT) gets the unique count of products.
- Join the ‘sales’ table with the ‘customer’ table to match the customer names on the customer id.
- Make sure your GROUP BY and ORDER BY clauses match so that you’re grouping and sorting on the same field.
- Limit the result to only show the top 5 customers.
Answer 2 Results
| customer_name | number_of_products |
|---|---|
| Jennifer Unbroken | 8 |
| Taylorswiftie Scissorhands | 7 |
| Galilea Galilei | 6 |
| Muhammad Dali | 6 |
| Eddie Redmaybe | 5 |
Answer 3: Monthly sales, sales growth rate, and next month’s sales prediction (Advanced)
Here’s my SQL. View the online version here.
WITH monthly_sales AS (
SELECT
DATE_TRUNC('month', sale_date) AS month,
SUM(quantity * price_per_unit) AS sales
FROM sales
GROUP BY 1
),
sales_growth AS (
SELECT
month,
sales,
LAG(sales) OVER (ORDER BY month) AS prev_month_sales,
((sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales)
OVER (ORDER BY month)) * 100 AS growth_rate
FROM monthly_sales
)
SELECT
month,
sales,
growth_rate,
LEAD(growth_rate) OVER (ORDER BY month) AS next_month_growth_rate,
sales * (1 + LEAD(growth_rate) OVER (ORDER BY month)/100) AS forecasted_sales
FROM sales_growth;
How it works
- The LAG and LEAD window functions compare each month’s sales to the previous and next months.
- The CTE (Common Table Expression) breaks the problem down into manageable parts.
- DATE_TRUNC function truncates the date to the month.
- Be careful with window function orders, ensure that you’re ordering by the correct field.
Answer 3 Results
| month | sales | growth_rate | next_month_growth_rate | forecasted_sales |
|---|---|---|---|---|
| 2022-07-01 | 15432.70 | null | 110.31 | 32457.83 |
| 2022-08-01 | 32457.83 | 110.31 | -46.06 | 17506.00 |
| 2022-09-01 | 17506.01 | -46.06 | 150.04 | 43773.25 |
| 2022-10-01 | 43773.25 | 150.04 | -6.69 | 40843.76 |
Practicing SQL problems can be a game-changer for your data analytics career.
What we covered in this guide:
- We tackled the initial intimidation in a data analytics career and showed how SQL practice boosts your confidence.
- You discovered how practicing SQL problems, from beginner to advanced, crushes impostor syndrome once and for all.
- We painted a vision of a promising data analytics career, where your SQL expertise directly shapes business strategy.
- We emphasized how investing time in SQL shifts your career from overwhelming to impactful.
Remember, SQL is your connection to the data universe.
Each exploration brings you closer to becoming a confident data analyst.
So, what’s the next SQL challenge you’re going to crush?