Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Before and After SQL as an example of writing cleaner SQL

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
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
);
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
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
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.