Before and After SQL as an example of writing cleaner SQL

_writing_clean_sql.md

Writing Clean SQL README

A contrived example to show some clean clode practices as they relate to SQL. In particular, the use of:

• Factoring out logical bits of work into common table expressions (CTE’s) like functions.
• CTE’s with good variable names
• Showing a flat structure to what would otherwise be a highly nested set of SQL statements, which become unreadable quickly

example_product_schema.sql

CREATE TABLE customers (
  customer_id bigint,
  first_name varchar,
  last_name varchar,
  phone varchar(50),
  email varchar,
  city varchar(50),
  state varchar(6),
  zip varchar(10),
  created_date date
);

CREATE TABLE products (
  product_id bigint,
  name varchar,
  description text,
  category varchar,
  price int,
  sku varchar
);

CREATE TABLE orders (
  order_id bigint,
  invoice_id varchar,
  customer_id bigint,
  product_id bigint,
  quantity int,
  order_date date,
  ship_date date
);

product_sales_after.sql

WITH product_order_totals AS (
  SELECT 
    p.product_id as product_id,
    p.name as product_name,
    p.price * o.quantity as product_order_total
  FROM 
    orders o
    INNER JOIN products p ON p.product_id=o.product_id
    INNER JOIN customers c ON c.customer_id=o.customer_id
  WHERE
    o.order_date >= dateadd(day, -90, CURRENT_DATE)
    AND c.state = 'CA'
),
product_sales_totals AS (
  SELECT 
    product_id,
    product_name,
    SUM(product_order_total) as sales_total
  FROM 
    product_order_totals p
  group by product_id,product_name
),
product_sales_percentage_of_total_sales AS (
  SELECT 
    p.product_id,
    p.product_name,
    SUM(sales_total) over () as total,
    ROUND((sales_total * 1.0) / (total * 1.0) * 100, 3) as percentage_of_total_sales
  FROM 
    product_sales_totals p
)
SELECT * FROM product_sales_percentage_of_total_sales

product_sales_before.sql

SELECT * 
FROM (
  SELECT 
    p2.product_id,
    p2.product_name,
    SUM(sales_total) over () as total,
    ROUND((sales_total * 1.0) / (total * 1.0) * 100, 3) as percentage_of_total_sales
  FROM 
    (SELECT 
      p1.product_id,
      p1.product_name,
      SUM(p1.product_order_total) as sales_total
    FROM 
      (SELECT 
        p.product_id as product_id,
        p.name as product_name,
        p.price * o.quantity as product_order_total
      FROM 
        orders o
        INNER JOIN products p ON p.product_id=o.product_id
        INNER JOIN customers c ON c.customer_id=o.customer_id
      WHERE
        o.order_date >= dateadd(day, -90, CURRENT_DATE)
        AND c.state = 'CA') p1
    GROUP BY product_id,product_name
    ) p2
)