Skip to content

Instantly share code, notes, and snippets.

@lundeen-bryan

lundeen-bryan/CTE.html

Created June 19, 2024 18:38
Show Gist options
  • Save lundeen-bryan/1bbc8d7009896ad1d207bc5969c6c9c7 to your computer and use it in GitHub Desktop.
Save lundeen-bryan/1bbc8d7009896ad1d207bc5969c6c9c7 to your computer and use it in GitHub Desktop.
Download ZIP
CTE_Notebook_Example
Raw
CTE.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SQL and Markdown Notebook</title>
<style>
body {
font-family: Arial, sans-serif;
margin: 20px;
line-height: 1.6;
}
h2 {
color: #333;
}
pre {
background: #f4f4f4;
border: 1px solid #ddd;
padding: 10px;
overflow-x: auto;
}
.copy-button {
background: #007bff;
border: none;
color: white;
padding: 5px 10px;
cursor: pointer;
border-radius: 3px;
margin-top: 5px;
}
.copy-button:hover {
background: #0056b3;
}
</style>
</head>
<body>
<h2>Retrieve a list of customers with their total order amounts in descending order</h2>
<pre>
WITH
Step1 AS (
SELECT cus.CustomerID
, cus.companyName
, SUM(orderdetails.quantity * orderdetails.unitPrice) AS TotalSales
FROM customers AS cus
JOIN orders USING(CustomerID)
JOIN orderdetails USING(orderId)
GROUP BY 1, 2)
SELECT *
FROM Step1
ORDER BY TotalSales DESC;
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Get the total sales for each product by category</h2>
<pre>
WITH Step1 AS (
SELECT
categoryName
, productName
, SUM(
orderdetails.quantity * orderdetails.unitPrice
) AS TotalSales
FROM
categories
INNER JOIN products USING (categoryId)
INNER JOIN orderdetails USING (productId)
GROUP BY 1,2
)
SELECT * FROM step1
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Get the top selling products by quantity sold</h2>
<pre>
SELECT
productName
, SUM(quantity) AS QuantitySold
FROM
products
INNER JOIN orderdetails USING (productId)
GROUP BY
1
ORDER BY QuantitySold DESC
LIMIT 10
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
WITH
Step1 AS (
SELECT Products.ProductName
, SUM(OrderDetails.Quantity) AS TotalQuantitySold
FROM Products
JOIN OrderDetails USING(productId)
GROUP BY Products.ProductName)
SELECT ProductName
, TotalQuantitySold
FROM Step1
ORDER BY TotalQuantitySold DESC
LIMIT 10
;
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Get the average unit price for each product in each category</h2>
<pre>
WITH Step1 AS (
SELECT
productName
, categoryName
, AVG(unitPrice) AS AvgPrice
FROM
products
INNER JOIN categories USING (categoryId)
GROUP BY 1, 2
)
SELECT * FROM Step1
ORDER BY AvgPrice DESC
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>What are the total sales and average sales per order for each category?</h2>
<pre>
WITH
Step1 AS (
SELECT categoryName AS "Category"
, SUM(orderdetails.unitPrice * orderdetails.quantity) AS TotalSales
, AVG(orderdetails.unitPrice) AS AvgSales
, COUNT(orderdetails.orderId) AS OrderCount
FROM orders
JOIN orderdetails USING(orderId)
JOIN products USING(productId)
JOIN categories USING(categoryId)
GROUP BY 1)
SELECT "Category"
, TotalSales
, AvgSales / OrderCount AS AvgOrderSales
FROM Step1
ORDER BY TotalSales DESC
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
WITH
category_sales AS (
SELECT Categories.CategoryName
, SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales
FROM Categories
JOIN Products
ON Categories.CategoryID = Products.CategoryID
JOIN OrderDetails
ON Products.ProductID = OrderDetails.ProductID
GROUP BY Categories.CategoryName)
SELECT category_sales.CategoryName
, category_sales.TotalSales
, category_sales.TotalSales / category_order_counts.OrderCount AS AverageOrderSales
FROM category_sales
JOIN (SELECT Categories.CategoryName
, COUNT(DISTINCT Orders.OrderID) AS OrderCount
FROM Categories
JOIN Products
ON Categories.CategoryID = Products.CategoryID
JOIN OrderDetails
ON Products.ProductID = OrderDetails.ProductID
JOIN Orders
ON OrderDetails.OrderID = Orders.OrderID
GROUP BY Categories.CategoryName) AS category_order_counts
ON category_sales.CategoryName = category_order_counts.CategoryName
ORDER BY TotalSales DESC
;
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>For each employee, what is their total sales and the total sales for the region they work in?</h2>
<pre>
WITH Step1 AS (
SELECT
CONCAT(firstname, ' ', lastname) AS "Employee"
, SUM(unitPrice * quantity) AS "Employee Sales"
, regiondescription AS "Region"
, SUM(unitPrice * quantity) OVER(partition by regionId ORDER BY unitPrice) AS "Regional Sales"
FROM
orders
INNER JOIN orderdetails USING (orderId)
INNER JOIN employees USING (employeeId)
INNER JOIN employeeterritories USING (employeeId)
INNER JOIN territories USING (territoryId)
INNER JOIN region USING (regionId)
GROUP BY
employeeId, orderDetailId, regionId
ORDER BY SUM(unitPrice * quantity) DESC
)
SELECT * FROM Step1
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
-- Step1 just pull the data as-is, Step2 aggregate the data,
-- then select what is needed for output
WITH
Step1 AS (
SELECT employees.employeeId AS `employeeId`
, employees.lastname AS `lastname`
, employees.firstname AS `firstname`
, orderdetails.unitPrice AS `unitPrice`
, orderdetails.quantity AS `quantity`
, region.regionId AS `regionId`
, region.regiondescription AS `regionDescription`
FROM orders
JOIN orderdetails
ON orders.orderId = orderdetails.orderId
JOIN employees
ON orders.employeeId = employees.employeeId
JOIN employeeterritories
ON employees.employeeId = employeeterritories.employeeId
JOIN territories
ON employeeterritories.territoryId = territories.territoryId
JOIN region
ON territories.regionId = region.regionId)
, Step2 AS (
SELECT CONCAT(`firstname`, ' ', `lastname`) AS `Employee`
, SUM(`unitPrice` * `quantity`) AS `Employee Sales`
, `regionDescription` AS `Region`
, SUM(`unitPrice` * `quantity`) OVER (PARTITION BY `regionId`) AS `Regional Sales`
FROM Step1
GROUP BY `employeeId`
, `regionId`
, `firstname`
, `lastname`
, `regionDescription`
, `unitPrice`
, `quantity`)
SELECT `Employee`
, `Employee Sales`
, `Region`
, `Regional Sales`
FROM Step2
ORDER BY `Region`
, `Employee Sales` DESC
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
SELECT
employees.employeeId AS `employeeid`
, employees.lastname AS `lastname`
, employees.firstname AS `firstname`
, orderdetails.unitPrice AS `unitprice`
, orderdetails.quantity AS `quantity`
, region.regionId AS `regionid`
, region.regiondescription AS `regiondescription`
FROM
orders
INNER JOIN orderdetails ON orders.orderId = orderdetails.orderId
INNER JOIN employees ON orders.employeeId = employees.employeeId
INNER JOIN employeeterritories ON employees.employeeId = employeeterritories.employeeId
INNER JOIN territories ON employeeterritories.territoryId = territories.territoryId
INNER JOIN region ON territories.regionId = region.regionId
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
--/*
WITH Step1 AS
(
/* This contains inner query & derived columns */
SELECT [column]
FROM [schema].[table]
)
,
Step2 AS
(
/* This contains outer querry from inner */
SELECT [column]
FROM Step1
)
SELECT
[column-alias]
,[column-alias]
FROM Step2 AS A
JOIN Step2 AS B
ON A.matching_column = B.matching_column
ORDER BY 1
--*/
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<pre>
--/*
WITH
Step1 AS (
-- This CTE step extracts necessary columns from a specified table
SELECT column1, column2, ...
FROM schema.table
WHERE condition -- Optional: specify conditions
),
Step2 AS (
-- This CTE step processes data from Step1
SELECT agg_func(column1) AS alias1, column2
FROM Step1
GROUP BY column2 -- Optional: Adjust grouping as necessary
),
-- Final SELECT to retrieve data from the last CTE step
SELECT
alias1, column2
FROM Step2
JOIN Step2 AS B -- Adjust join type and conditions based on requirements
ON Step2.matching_column = B.matching_column
ORDER BY order_column -- Replace '1' with specific column name or alias
--*/
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Find the top 5 customers by total spending</h2>
<pre>
WITH
Step1 AS (
SELECT companyName AS "Customer"
, unitPrice
, quantity
FROM customers
JOIN orders USING(CustomerID)
JOIN orderdetails USING(orderId))
,
Step2 AS (SELECT SUM(Step1.unitPrice * Step1.quantity) AS "Total Spending"
, Step1.`Customer`
FROM Step1
GROUP BY Step1.`Customer`
)
SELECT `Customer`
, `Total Spending`
FROM Step2
ORDER BY `Total Spending` DESC
, `Customer`
LIMIT 5
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Find the top 5 products by total sales, for products that have never been discontinued</h2>
<pre>
WITH
Step1 AS (
SELECT productName
, discontinued
, orderdetails.unitPrice
, orderdetails.quantity
FROM products
JOIN orderdetails USING(productId)
WHERE 1=1
AND discontinued = 1)
, Step2 AS (
SELECT productName
, SUM(Step1.unitPrice * Step1.quantity) AS `Total Sales`
FROM Step1
GROUP BY productName)
SELECT productName AS `Product`
, `Total Sales`
FROM Step2
ORDER BY `Total Sales` DESC
LIMIT 5
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<h2>Find the average order value for each customer</h2>
<pre>
WITH Step1 AS (
SELECT CustomerID, OrderID, SUM(UnitPrice * Quantity) AS OrderValue
FROM orderdetails
GROUP BY CustomerID, OrderID
),
Step2 AS (
SELECT CustomerID, AVG(OrderValue) AS AvgOrderValue
FROM Step1
GROUP BY CustomerID
)
SELECT customers.CustomerID, customers.CompanyName, Step2.AvgOrderValue
FROM Step2
JOIN customers ON Step2.CustomerID = customers.CustomerID
ORDER BY AvgOrderValue DESC
</pre>
<button class="copy-button" onclick="copyToClipboard(this)">Copy SQL</button>
<script>
function copyToClipboard(button) {
const codeBlock = button.previousElementSibling;
const textArea = document.createElement('textarea');
textArea.value = codeBlock.innerText;
document.body.appendChild(textArea);
textArea.select();
document.execCommand('copy');
document.body.removeChild(textArea);
button.innerText = 'Copied!';
setTimeout(() => {
button.innerText = 'Copy SQL';
}, 2000);
}
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment