Category: Projects

# Install via Homebrew
brew install --cask tableplus

# Install via Homebrew
brew install --cask pgadmin4

# Install via Homebrew
brew install --cask postico

   SELECT COUNT(*) FROM users;
   SELECT COUNT(*) FROM orders;
   SELECT COUNT(*) FROM products;

   SELECT 
     u.first_name, u.last_name, 
     COUNT(o.id) as order_count
   FROM users u 
   LEFT JOIN orders o ON u.id = o.user_id 
   GROUP BY u.id, u.first_name, u.last_name
   ORDER BY order_count DESC;

brew install --cask tableplus

-- 1. View all users
SELECT * FROM users LIMIT 10;

-- 2. Count total records
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

-- 3. Filter data
SELECT first_name, last_name, email 
FROM users 
WHERE gender = 'female' 
LIMIT 10;

-- 4. Sort data
SELECT first_name, last_name, date_of_birth 
FROM users 
ORDER BY date_of_birth DESC 
LIMIT 10;

-- 5. Filter with conditions
SELECT title, price, category 
FROM products 
WHERE price > 50 AND category = 'men' 
ORDER BY price DESC;

-- 1. Count by category
SELECT category, COUNT(*) as product_count 
FROM products 
GROUP BY category;

-- 2. Average prices by category
SELECT category, 
       AVG(price) as avg_price,
       MIN(price) as min_price,
       MAX(price) as max_price
FROM products 
GROUP BY category;

-- 3. Users by gender
SELECT gender, COUNT(*) as user_count 
FROM users 
WHERE gender IS NOT NULL
GROUP BY gender;

-- 4. Products with low stock
SELECT COUNT(*) as low_stock_products 
FROM products 
WHERE stock_quantity < 10;

-- 1. Users with their orders
SELECT u.first_name, u.last_name, u.email, o.id as order_id, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.created_at DESC
LIMIT 20;

-- 2. Orders with product details
SELECT o.id as order_id, 
       p.title as product_name, 
       p.price, 
       p.category,
       o.created_at
FROM orders o
INNER JOIN products p ON o.product_id = p.id
ORDER BY o.created_at DESC
LIMIT 20;

-- 3. Complete order information (3-table join)
SELECT u.first_name, u.last_name,
       p.title as product_name,
       p.price,
       p.category,
       o.created_at as order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id
ORDER BY o.created_at DESC
LIMIT 20;

-- 1. All users and their order count (including users with no orders)
SELECT u.first_name, u.last_name, u.email,
       COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.first_name, u.last_name, u.email
ORDER BY order_count DESC;

-- 2. Users who haven't placed any orders
SELECT u.first_name, u.last_name, u.email, u.created_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL
ORDER BY u.created_at DESC;

-- 3. Products that have never been ordered
SELECT p.title, p.price, p.category, p.stock_quantity
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL
ORDER BY p.price DESC;

-- 1. Top customers by order count
SELECT u.first_name, u.last_name,
       COUNT(o.id) as total_orders,
       SUM(p.price) as total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(o.id) > 1
ORDER BY total_spent DESC
LIMIT 10;

-- 2. Most popular products
SELECT p.title, p.category, p.price,
       COUNT(o.id) as times_ordered,
       SUM(p.price) as total_revenue
FROM products p
INNER JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.title, p.category, p.price
ORDER BY times_ordered DESC
LIMIT 10;

-- 3. Monthly order analysis
SELECT DATE_TRUNC('month', o.created_at) as month,
       COUNT(o.id) as order_count,
       COUNT(DISTINCT o.user_id) as unique_customers,
       SUM(p.price) as total_revenue
FROM orders o
INNER JOIN products p ON o.product_id = p.id
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month;

-- 1. Students with their course and school info
SELECT u.first_name, u.last_name,
       c.title as course_name,
       s.title as school_name,
       st.enrolment_date
FROM students st
INNER JOIN users u ON st.user_id = u.id
INNER JOIN courses c ON st.course_id = c.id
INNER JOIN schools s ON st.school_id = s.id
ORDER BY st.enrolment_date DESC
LIMIT 20;

-- 2. Course popularity by school
SELECT s.title as school_name,
       c.title as course_name,
       COUNT(st.id) as student_count
FROM students st
INNER JOIN courses c ON st.course_id = c.id
INNER JOIN schools s ON st.school_id = s.id
GROUP BY s.id, s.title, c.id, c.title
ORDER BY student_count DESC;

-- 3. Schools with enrollment stats
SELECT s.title as school_name,
       COUNT(st.id) as total_students,
       COUNT(DISTINCT st.course_id) as courses_offered,
       MIN(st.enrolment_date) as first_enrollment,
       MAX(st.enrolment_date) as latest_enrollment
FROM schools s
LEFT JOIN students st ON s.id = st.school_id
GROUP BY s.id, s.title
ORDER BY total_students DESC;

-- 1. Subqueries: Users who spent more than average
WITH user_spending AS (
  SELECT u.id, u.first_name, u.last_name,
         SUM(p.price) as total_spent
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id
  INNER JOIN products p ON o.product_id = p.id
  GROUP BY u.id, u.first_name, u.last_name
)
SELECT first_name, last_name, total_spent
FROM user_spending
WHERE total_spent > (SELECT AVG(total_spent) FROM user_spending)
ORDER BY total_spent DESC;

-- 2. Window functions: Ranking customers
SELECT u.first_name, u.last_name,
       COUNT(o.id) as order_count,
       SUM(p.price) as total_spent,
       RANK() OVER (ORDER BY SUM(p.price) DESC) as spending_rank
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
GROUP BY u.id, u.first_name, u.last_name
ORDER BY spending_rank
LIMIT 20;

-- 3. Case statements for categorization
SELECT u.first_name, u.last_name,
       COUNT(o.id) as order_count,
       CASE 
         WHEN COUNT(o.id) >= 5 THEN 'VIP Customer'
         WHEN COUNT(o.id) >= 2 THEN 'Regular Customer'
         ELSE 'New Customer'
       END as customer_type
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.first_name, u.last_name
ORDER BY order_count DESC;

-- 1. Find users enrolled in the same course (pseudo self-join)
SELECT DISTINCT 
       u1.first_name || ' ' || u1.last_name as student1,
       u2.first_name || ' ' || u2.last_name as student2,
       c.title as course_name
FROM students s1
INNER JOIN students s2 ON s1.course_id = s2.course_id AND s1.user_id < s2.user_id
INNER JOIN users u1 ON s1.user_id = u1.id
INNER JOIN users u2 ON s2.user_id = u2.id
INNER JOIN courses c ON s1.course_id = c.id
ORDER BY c.title, student1
LIMIT 20;

-- 2. Complex business question: Multi-role users
SELECT u.first_name, u.last_name, u.email,
       COUNT(DISTINCT o.id) as orders_placed,
       COUNT(DISTINCT st.id) as courses_enrolled,
       CASE 
         WHEN COUNT(DISTINCT o.id) > 0 AND COUNT(DISTINCT st.id) > 0 THEN 'Customer & Student'
         WHEN COUNT(DISTINCT o.id) > 0 THEN 'Customer Only'
         WHEN COUNT(DISTINCT st.id) > 0 THEN 'Student Only'
         ELSE 'No Activity'
       END as user_type
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN students st ON u.id = st.user_id
GROUP BY u.id, u.first_name, u.last_name, u.email
ORDER BY orders_placed DESC, courses_enrolled DESC;