Alex The Analyst

The 4 Real Levels of SQL...

Level 1: The "Beginner"

```
SELECT customer_id, name, email
FROM customers
WHERE country = 'USA';
```

Level 2: The "Just Completed a Course"

```
SELECT r.region_name, COUNT(DISTINCT o.order_id) AS order_count
FROM orders o
JOIN regions r ON o.region_id = r.region_id
WHERE o.order_date >= '2024-01-01'
GROUP BY r.region_name
ORDER BY order_count DESC;
```

Level 3: The "Landed Your First Job"

```
SELECT customer_id,
order_id,
order_total,
order_date,
RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rank_by_spend,
CASE
WHEN order_total > 500 THEN 'High Value'
WHEN order_total BETWEEN 200 AND 500 THEN 'Medium Value'
ELSE 'Low Value'
END AS order_tier
FROM orders
WHERE order_status = 'Completed'
AND order_date BETWEEN '2024-01-01' AND '2024-12-31';
```

Level 4: The "Used to for Years"

```
CREATE TEMP TABLE high_value_customers AS
SELECT
customer_id
FROM orders
WHERE order_total IS NOT NULL
GROUP BY customer_id
HAVING SUM(order_total) > 20000
AND COUNT(DISTINCT order_id) >= 5;

WITH recent_orders AS (
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
AND order_total > 0
AND order_status IN ('Completed', 'Shipped')
),
clean_customers AS (
SELECT
customer_id,
INITCAP(name) AS name_cleaned,
LOWER(TRIM(email)) AS email_cleaned,
COALESCE(loyalty_status, 'Standard') AS loyalty_status
FROM customers
WHERE email IS NOT NULL
),
enriched_orders AS (
SELECT
ro.order_id,
ro.customer_id,
ro.order_date,
ro.order_total,
ro.product_id,
cc.name_cleaned,
cc.email_cleaned,
cc.loyalty_status
FROM recent_orders ro
JOIN clean_customers cc ON ro.customer_id = cc.customer_id
),
orders_with_products AS (
SELECT eo.*, p.product_name, p.category
FROM enriched_orders eo
LEFT JOIN products p ON eo.product_id = p.product_id
)
SELECT *
FROM orders_with_products
WHERE customer_id IN (SELECT customer_id FROM high_value_customers)
ORDER BY order_total DESC, order_date DESC;
```

1 week ago | [YT] | 1,125