Business Intelligence Exercises for Beginners: Joins, Windows, CTEs

Welcome to the exciting world of business intelligence! If you’re just starting out and want to learn some basics, you’re in the right place. Today, we’ll explore some fun and simple BI exercises using Joins, Window Functions, and CTEs (Common Table Expressions). Don’t worry — it’s easier than it sounds.

Let’s imagine you’ve just joined a company’s analytics team. You’ve got access to a database full of interesting stuff: customers, orders, products, and employees. But how do you turn that into insights?

Start With Joins — Combining Tables Like a Pro

Think of joins as a way to mix and match data from different tables, like adding toppings to a pizza.

Let’s take two tables:

• customers — contains customer details
• orders — contains orders made by customers

We want to see which customer made which order. Easy!

SELECT customers.name, orders.order_date
FROM customers
JOIN orders ON customers.id = orders.customer_id;

This INNER JOIN shows only customers who have placed an order. It combines the tables on matching customer IDs.

But what if we want all customers, even those who haven’t ordered yet?

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

This is a LEFT JOIN, and it keeps all customers — even those without orders, leaving NULL for missing data.

Quick Join Exercise

Use this data:

• Table products: id, name, price
• Table order_items: id, order_id, product_id, quantity

Try this challenge:

Question: Show each product’s name and the total quantity ordered. Start with an INNER JOIN.

(Hint: You’ll need GROUP BY too!)

Window Functions — Analytics on Steroids

Window functions let you do magic — like calculating rankings and moving averages — without collapsing your data into one row per group.

Imagine you want to rank employees in each department based on sales. Here’s how!

SELECT 
  name, 
  department, 
  sales,
  RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS sales_rank
FROM employees;

Whoa, did you see that? We kept all the employee data but added a new column that shows their rank in their department!

Let’s break it down:

• RANK(): Gives ranking numbers.
• OVER(): Tells SQL how to make the rank.
• PARTITION BY department: Reset rank for each department.
• ORDER BY sales DESC: Highest sales = rank 1

Another Fun Example: Running Total

Want to see how your revenue adds up over time?

SELECT 
  order_date,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Each row shows how your revenue grew — this is super useful in dashboards!

Window Function Exercise

Use this data:

• Table orders: id, customer_id, order_date, amount

Try this challenge:

Question: For each customer, how many orders have they placed so far? Create a running count of orders per customer over time!

CTEs — SQL Made Cleaner

CTEs (Common Table Expressions) are like giving your SQL a nickname. Instead of writing one giant, confusing query, break your logic into steps.

Let’s build a simple one. Suppose you want to find customers with more than 3 orders.

WITH customer_order_count AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id
FROM customer_order_count
WHERE order_count > 3;

Here’s what we just did:

• Step 1: Create a little table using WITH to count orders
• Step 2: Use that table to filter customers with over 3 orders

This approach is clean, readable, and easy to debug.

CTE Exercise

Use these tables:

• products: id, name, price
• order_items: order_id, product_id, quantity

Try this challenge:

Question: Use a CTE to calculate total revenue per product. Then show products that made more than $1,000.

Putting It All Together

Ready for something bigger? Let’s combine Joins, Windows, and CTEs in one challenge. Don’t worry — you’ve got this!

Final Challenge

1. Start with a CTE that joins orders and order_items to compute order totals.
2. Use that CTE to rank customers by their total spend using a window function.
3. Output the top 5 customers based on total amount!

This type of logic is seen in real dashboards. Great BI teams use these tools daily to produce insights!

Why These Skills Matter

If you’re hoping to work in data, these skills are golden. They’re used in:

• Marketing dashboards (Which campaigns worked best?)
• Sales reports (Top 10 products this month?)
• Customer behavior analysis (Who’s our VIP?)

Tools like Power BI and Tableau love data that’s already clean. SQL helps get it there.

Tips to Keep Going

Here are some final tips to practice:

• Use a sample dataset like Sakila or Northwind.
• Try making up questions for yourself — be curious!
• Join communities like r/SQL or Stack Overflow for help.

Most importantly, don’t be afraid to make mistakes. That’s how learning happens!

You Did It!

By now, you’ve explored:

• Joins: Combine related data
• Window Functions: Do advanced calculations without simplifying your table
• CTEs: Break your queries into easy steps

That’s a solid foundation in business intelligence. Keep practicing, and soon you’ll be slicing and dicing data like a pro.

Now, go impress your manager or ace that BI interview!