Skip to content
Open
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
129 changes: 116 additions & 13 deletions 02_activities/homework/homework_4.sql
Original file line number Diff line number Diff line change
Expand Up @@ -17,7 +17,9 @@ The `||` values concatenate the columns into strings.
Edit the appropriate columns -- you're making two edits -- and the NULL rows will be fixed.
All the other rows will remain the same.) */


SELECT
product_name || ', ' || coalesce(product_size, '') || ' (' || coalesce(product_qty_type, 'unit') || ')'
FROM product;


--Windowed Functions
Expand All @@ -30,16 +32,40 @@ each new market date for each customer, or select only the unique market dates p
(without purchase details) and number those visits.
HINT: One of these approaches uses ROW_NUMBER() and one uses DENSE_RANK(). */

SELECT
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date
) AS visit,
cp.*
FROM customer_purchases cp
-- ORDER BY customer_id, market_date
;

/* 2. Reverse the numbering of the query from a part so each customer’s most recent visit is labeled 1,
then write another query that uses this one as a subquery (or temp table) and filters the results to
only the customer’s most recent visit. */

SELECT * FROM (
SELECT
DENSE_RANK() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS recent_visit,
cp.*
FROM customer_purchases cp
) AS ranked
WHERE ranked.recent_visit = 1;

/* 3. Using a COUNT() window function, include a value along with each row of the
customer_purchases table that indicates how many different times that customer has purchased that product_id. */


SELECT
COUNT(*) OVER (
PARTITION BY customer_id, product_id
) as purchased_count,
cp.*
FROM customer_purchases cp;


-- String manipulations
Expand All @@ -54,22 +80,99 @@ Remove any trailing or leading whitespaces. Don't just use a case statement for

Hint: you might need to use INSTR(product_name,'-') to find the hyphens. INSTR will help split the column. */


SELECT
p.*,
CASE
WHEN instr(product_name, '-') > 0 THEN
trim(substr(product_name, instr(product_name, '-') + 1))
ELSE NULL
END as description
FROM product p;

/* 2. Filter the query to show any product_size value that contain a number with REGEXP. */


SELECT
p.*,
CASE
WHEN instr(product_name, '-') > 0 THEN
trim(substr(product_name, instr(product_name, '-') + 1))
ELSE NULL
END as description
FROM product p
WHERE p.product_size REGEXP '\d+';

-- UNION
/* 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.

HINT: There are a possibly a few ways to do this query, but if you're struggling, try the following:
1) Create a CTE/Temp Table to find sales values grouped dates;
2) Create another CTE/Temp table with a rank windowed function on the previous query to create
"best day" and "worst day";
3) Query the second temp table twice, once for the best day, once for the worst day,
with a UNION binding them. */


-- 1. Using a UNION, write a query that displays the market dates with the highest and lowest total sales.
--
-- HINT: There are a possibly a few ways to do this query, but if you're struggling, try the following:
-- 1) Create a CTE/Temp Table to find sales values grouped dates;
-- 2) Create another CTE/Temp table with a rank windowed function on the previous query to create
-- "best day" and "worst day";
-- 3) Query the second temp table twice, once for the best day, once for the worst day,
-- with a UNION binding them.

-- option "Do as you are told!"
WITH
_daily_sales AS (
SELECT
market_date,
sum(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
),
_ranked_daily_sales AS (
SELECT
*,
rank() OVER (ORDER BY total_sales DESC) AS best,
rank() OVER (ORDER BY total_sales ASC) AS worst
FROM _daily_sales
)
SELECT 'best' as label, market_date, total_sales
FROM _ranked_daily_sales
WHERE best = 1
UNION
SELECT 'worst' as label, market_date, total_sales
FROM _ranked_daily_sales
WHERE worst = 1;

-- option "Look ma: without UNION!"
WITH
_daily_sales AS (
SELECT
market_date,
sum(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
),
_ranked__daily_sales AS (
SELECT
*,
rank() OVER (ORDER BY total_sales DESC) AS best,
rank() OVER (ORDER BY total_sales ASC) AS worst
FROM _daily_sales
)
SELECT iif(best = 1, 'best', 'worst') AS label, market_date, total_sales
FROM _ranked__daily_sales
WHERE best = 1 OR worst = 1;

-- option "Subqueries? Sure, why not?"
WITH
_daily_sales AS (
SELECT
market_date,
sum(quantity * cost_to_customer_per_qty) AS total_sales
FROM customer_purchases
GROUP BY market_date
)
-- best day
SELECT 'best' as label, market_date, total_sales
FROM _daily_sales
WHERE total_sales = (SELECT max(total_sales) FROM _daily_sales)
UNION
-- worst day
SELECT 'worst' as label, market_date, total_sales
FROM _daily_sales
WHERE total_sales = (SELECT min(total_sales) FROM _daily_sales);