PostgreSQL for Data Engineers

CREATE DATABASE freshmart_db;

CREATE TABLE stores (
  store_id    VARCHAR(10)  PRIMARY KEY,
  store_name  VARCHAR(100) NOT NULL,
  city        VARCHAR(100) NOT NULL,
  state       VARCHAR(50)  NOT NULL,
  opened_date DATE
);

CREATE TABLE sales (
  order_id      VARCHAR(20)    PRIMARY KEY,
  store_id      VARCHAR(10)    NOT NULL REFERENCES stores(store_id),
  product_name  VARCHAR(200)   NOT NULL,
  category      VARCHAR(100),
  quantity      INTEGER        NOT NULL DEFAULT 1,
  unit_price    NUMERIC(10,2)  NOT NULL,
  total_amount  NUMERIC(10,2)  GENERATED ALWAYS AS (quantity * unit_price) STORED,
  order_date    DATE           NOT NULL,
  created_at    TIMESTAMP      DEFAULT NOW()
);

INSERT INTO stores (store_id, store_name, city, state, opened_date)
VALUES
  ('ST001', 'FreshMart New Delhi',   'New Delhi',  'Delhi',     '2020-01-15'),
  ('ST002', 'FreshMart Mumbai',      'Mumbai',     'Maharashtra','2020-03-10'),
  ('ST003', 'FreshMart Bangalore',   'Bangalore',  'Karnataka', '2020-06-01'),
  ('ST004', 'FreshMart Chennai',     'Chennai',    'Tamil Nadu','2021-01-20'),
  ('ST005', 'FreshMart Hyderabad',   'Hyderabad',  'Telangana', '2021-04-05');

INSERT INTO sales (order_id, store_id, product_name, category, quantity, unit_price, order_date)
VALUES
  ('ORD1001', 'ST001', 'Basmati Rice 5kg',    'Grocery',      12,  299.00, '2024-01-15'),
  ('ORD1002', 'ST001', 'Samsung TV 43inch',   'Electronics',   2, 32000.00,'2024-01-15'),
  ('ORD1003', 'ST001', 'Amul Butter 500g',    'Dairy',        25,  240.00, '2024-01-15'),
  ('ORD1004', 'ST002', 'Sunflower Oil 1L',    'Grocery',      18,  145.00, '2024-01-15'),
  ('ORD1005', 'ST002', 'iPhone 14',           'Electronics',   1, 75000.00,'2024-01-15'),
  ('ORD1006', 'ST002', 'Amul Milk 1L',        'Dairy',        40,   62.00, '2024-01-15'),
  ('ORD1007', 'ST003', 'Nike Running Shoes',  'Apparel',       5,  4500.00,'2024-01-16'),
  ('ORD1008', 'ST003', 'Colgate Toothpaste',  'Personal Care',30,   89.00, '2024-01-16'),
  ('ORD1009', 'ST004', 'Levis Jeans',         'Apparel',       8,  2999.00,'2024-01-16'),
  ('ORD1010', 'ST005', 'Dove Soap 100g',      'Personal Care',50,   65.00, '2024-01-16');

-- Get every row and every column
SELECT * FROM sales;

-- Get only specific columns
SELECT order_id, product_name, total_amount FROM sales;

-- Filter rows with WHERE
SELECT * FROM sales WHERE category = 'Electronics';

-- Multiple conditions
SELECT * FROM sales WHERE category = 'Grocery' AND unit_price > 100;

-- Sort results
SELECT * FROM sales ORDER BY total_amount DESC;

-- Limit number of results
SELECT * FROM sales ORDER BY order_date DESC LIMIT 5;

-- Filter by date
SELECT * FROM sales WHERE order_date = '2024-01-15';

-- Filter by date range
SELECT * FROM sales WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Count all rows
SELECT COUNT(*) FROM sales;

-- Sum of all sales
SELECT SUM(total_amount) FROM sales;

-- Average order value
SELECT ROUND(AVG(total_amount), 2) AS avg_order_value FROM sales;

-- Highest and lowest sale
SELECT MAX(total_amount) AS highest, MIN(total_amount) AS lowest FROM sales;

-- Total sales per category  ← this is GROUP BY
SELECT
  category,
  COUNT(*)              AS order_count,
  SUM(total_amount)     AS total_revenue,
  ROUND(AVG(unit_price),2) AS avg_price
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

-- WHERE filters individual rows before grouping
SELECT category, SUM(total_amount) AS revenue
FROM sales
WHERE order_date >= '2024-01-01'    -- ← filters rows first
GROUP BY category;

-- HAVING filters groups after aggregation
SELECT category, SUM(total_amount) AS revenue
FROM sales
GROUP BY category
HAVING SUM(total_amount) > 5000;    -- ← only show categories with revenue > 5000

-- INNER JOIN — sales with their store name
SELECT
  s.order_id,
  s.product_name,
  s.total_amount,
  st.store_name,
  st.city
FROM sales s
INNER JOIN stores st ON s.store_id = st.store_id;

-- Total revenue per city
SELECT
  st.city,
  COUNT(s.order_id)       AS total_orders,
  SUM(s.total_amount)     AS total_revenue
FROM stores st
LEFT JOIN sales s ON st.store_id = s.store_id
GROUP BY st.city
ORDER BY total_revenue DESC NULLS LAST;

-- UPDATE a single row
UPDATE stores
SET city = 'Bengaluru'
WHERE store_id = 'ST003';

-- UPDATE multiple rows
UPDATE sales
SET category = 'Food & Grocery'
WHERE category = 'Grocery';

-- DELETE a specific row
DELETE FROM sales WHERE order_id = 'ORD1001';

-- DELETE with a condition
DELETE FROM sales WHERE order_date < '2023-01-01';

INSERT INTO stores (store_id, store_name, city, state)
VALUES ('ST001', 'FreshMart New Delhi Updated', 'New Delhi', 'Delhi')
ON CONFLICT (store_id)
DO UPDATE SET
  store_name = EXCLUDED.store_name,
  city       = EXCLUDED.city;
-- If ST001 exists → UPDATE it. If not → INSERT it.

WITH daily_totals AS (
  SELECT
    order_date,
    SUM(total_amount) AS daily_revenue
  FROM sales
  GROUP BY order_date
),
high_revenue_days AS (
  SELECT * FROM daily_totals
  WHERE daily_revenue > 50000
)
SELECT * FROM high_revenue_days ORDER BY daily_revenue DESC;

-- Rank products by revenue within each category
SELECT
  product_name,
  category,
  total_amount,
  RANK() OVER (PARTITION BY category ORDER BY total_amount DESC) AS rank_in_category
FROM sales;

-- Running total of revenue by date
SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM sales
ORDER BY order_date;

-- Create a Gold-layer summary table
CREATE TABLE gold_sales_by_store_category AS
SELECT
  st.store_id,
  st.city,
  s.category,
  COUNT(s.order_id)           AS total_orders,
  SUM(s.total_amount)         AS total_revenue,
  ROUND(AVG(s.unit_price), 2) AS avg_unit_price,
  MIN(s.order_date)           AS first_sale_date,
  MAX(s.order_date)           AS last_sale_date
FROM sales s
INNER JOIN stores st ON s.store_id = st.store_id
GROUP BY st.store_id, st.city, s.category;