If you’ve already learned the basics of PostgreSQL and want to take the next step, this tutorial is for you. Instead of reading dry documentation, we’ll continue learning through real-world problem solving — just like developers do in real projects.

We’ll still use the same simple online store example with these tables:

• users

• products

• orders

And now we’ll add one more:

• categories

Let’s jump right into practical problems 👇

---

🟢 Problem #1 — How Do We Organize Products Into Categories?

In the real world, products are rarely random. You group them into categories like:

• Electronics

• Books

• Clothing

• Accessories

So first, let’s create a category table:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  category_name VARCHAR(120) NOT NULL
);

Then link products to categories:

ALTER TABLE products 
ADD COLUMN category_id INT REFERENCES categories(id);

Now every product can belong to a category — which makes filtering, analytics, and navigation much easier.

---

🟡 Problem #2 — How Do We Show Each Product With Its Category?

This is where JOIN becomes useful.

SELECT 
  p.product_name,
  c.category_name,
  p.price
FROM products p
LEFT JOIN categories c 
  ON p.category_id = c.id;

Why LEFT JOIN?

Because some products might not have a category yet — and we still want them to appear.

Very common in:

✔ dashboards
✔ APIs
✔ admin panels

---

🔵 Problem #3 — What Is the Most Purchased Product?

Let’s build a real analytics query.

SELECT 
  p.product_name,
  SUM(o.quantity) AS total_sold
FROM orders o
JOIN products p 
  ON o.product_id = p.id
GROUP BY p.product_name
ORDER BY total_sold DESC
LIMIT 1;

This gives you the top-selling product.
You’ve just built a feature used by almost every e-commerce site 😎

---

🟣 Problem #4 — Using CTE (WITH) to Simplify Complex Queries

Sometimes SQL queries get long and messy. That’s when CTEs (Common Table Expressions) make life easier.

Let’s say we want:

✔ total spending per user
✔ but we only want users who spent more than $500

WITH spending AS (
  SELECT 
    u.id,
    u.name,
    SUM(p.price * o.quantity) AS total_spent
  FROM orders o
  JOIN users u ON u.id = o.user_id
  JOIN products p ON p.id = o.product_id
  GROUP BY u.id, u.name
)

SELECT * 
FROM spending
WHERE total_spent > 500
ORDER BY total_spent DESC;

This is cleaner, easier to read, and easier to maintain.
Future you will thank present you.

---

🔴 Problem #5 — How Do We Detect Users Who Haven’t Bought Anything Recently?

Let’s define “inactive users” as those with no orders in the last 90 days.

SELECT 
  u.id,
  u.name,
  u.email
FROM users u
LEFT JOIN orders o 
  ON u.id = o.user_id 
  AND o.created_at > NOW() - INTERVAL '90 days'
WHERE o.id IS NULL;

Congratulations — you just built a re-engagement user list.
This is extremely useful for email marketing and retention campaigns.

---

🟠 Problem #6 — How Do We Find the Latest Order for Each User?

This looks complicated — until you use a subquery.

SELECT 
  u.name,
  o.id AS order_id,
  o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at = (
  SELECT MAX(created_at)
  FROM orders
  WHERE user_id = u.id
);

This is perfect for:

✔ dashboards
✔ CRM systems
✔ activity feeds

---

🟤 Problem #7 — How Do We Prevent Bad Data in the First Place?

One of the biggest mistakes beginners make is trusting the app to validate data.
But databases should also protect themselves.

Example: Price must be positive

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

Example: Quantity must be at least 1

ALTER TABLE orders
ADD CONSTRAINT qty_positive CHECK (quantity >= 1);

Now even if your app crashes or bugs —
your data remains safe.

---

⭐ Best Practice Corner

Here are some developer-friendly PostgreSQL habits:

✔ Use readable SQL — your future self will thank you
✔ Prefer CTEs when queries become long
✔ Use LEFT JOIN when data may not exist
✔ Always index foreign keys
✔ Validate important rules at the database level

Small habits → big reliability.