ADVANCED SQL

CASE STUDIES
DATA ANALYTICS | SQL CASE STUDY

01

SQL CASE STUDY 1

Consider the following schema for this SQL case study

The sales table captures all customer_id level purchases

with and corresponding order_date and product_id
information for when and what menu items were
ordered.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

02

The menu table maps the product_id to the actual

product_name and price of each menu item.

The final members table captures the join_date when a

customer_id joined the beta version of the Danny’s Diner
loyalty program.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

03
TRY FOLLOWING QUESTIONS

1. What is the total amount each customer spent at the

restaurant?
2. How many days has each customer visited the restaurant?
3. What was the first item from the menu purchased by each
customer?
4. What is the most purchased item on the menu and how
many times was it purchased by all customers?
5. Which item was the most popular for each customer?
6. Which item was purchased first by the customer after they
became a member?
7. Which item was purchased just before the customer became
a member?
8. What is the total items and amount spent for each member
before they became a member?
9. If each $1 spent equates to 10 points and sushi has a 2x
points multiplier - how many points would each customer
have?
10. In the first week after a customer joins the program
(including their join date) they earn 2x points on all items, not
just sushi - how many points do customer A and B have at
the end of January?
11. Join All the Table ?
12. Rank All the Table?

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

04
1. What is the total amount each customer spent at
the restaurant?
SELECT s.customer_id, SUM(price) AS total_sales
FROM dbo.sales AS s
JOIN dbo.menu AS m
ON s.product_id = m.product_id
GROUP BY customer_id;

2. How many days has each customer visited the

restaurant?
SELECT customer_id, COUNT(DISTINCT(order_date)) AS
visit_count
FROM dbo.sales
GROUP BY customer_id;

3. What was the first item from the menu

purchased by each customer?
WITH ordered_sales_cte AS
(
SELECT customer_id, order_date, product_name,
DENSE_RANK() OVER(PARTITION BY s.customer_id
ORDER BY s.order_date) AS rank

FROM dbo.sales AS s
JOIN dbo.menu AS m
ON s.product_id = m.product_id
)

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

05
SELECT customer_id, product_name
FROM ordered_sales_cte
WHERE rank = 1
GROUP BY customer_id, product_name;

4. What is the most purchased item on the menu and

how many times was it purchased by all customers?
SELECT TOP 1 (COUNT(s.product_id)) AS most_purchased,
product_name
FROM dbo.sales AS s
JOIN dbo.menu AS m
ON s.product_id = m.product_id
GROUP BY s.product_id, product_name
ORDER BY most_purchased DESC;SC;

5. Which item was the most popular for each customer?

WITH fav_item_cte AS
(
SELECT s.customer_id, m.product_name,
COUNT(m.product_id) AS order_count,
DENSE_RANK() OVER(PARTITION BY s.customer_id
ORDER BY COUNT(s.customer_id) DESC) AS rank
FROM dbo.menu AS m
JOIN dbo.sales AS s
ON m.product_id = s.product_id
GROUP BY s.customer_id, m.product_name
)

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

06
SELECT customer_id, product_name, order_count
FROM fav_item_cte
WHERE rank = 1;

6. Which item was purchased first by the customer after

they became a member?
WITH member_sales_cte AS
(
SELECT s.customer_id, m.join_date, s.order_date, s.product_id,
DENSE_RANK() OVER(PARTITION BY s.customer_id
ORDER BY s.order_date) AS rank
FROM sales AS s
JOIN members AS m
ON s.customer_id = m.customer_id
WHERE s.order_date >= m.join_date
)
SELECT s.customer_id, s.order_date, m2.product_name
FROM member_sales_cte AS s
JOIN menu AS m2
ON s.product_id = m2.product_id
WHERE rank = 1;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

07
7. Which item was purchased just before the customer
became a member?
WITH prior_member_purchased_cte AS
(
SELECT s.customer_id, m.join_date, s.order_date, s.product_id,
DENSE_RANK() OVER(PARTITION BY s.customer_id
ORDER BY s.order_date DESC) AS rank
FROM sales AS s
JOIN members AS m
ON s.customer_id = m.customer_id
WHERE s.order_date < m.join_date
)
SELECT s.customer_id, s.order_date, m2.product_name
FROM prior_member_purchased_cte AS s
JOIN menu AS m2
ON s.product_id = m2.product_id
WHERE rank = 1;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

08
8. What is the total items and amount spent for each
member before they became a member?
SELECT s.customer_id, COUNT(DISTINCT s.product_id) AS
unique_menu_item, SUM(mm.price) AS total_sales
FROM sales AS s
JOIN members AS m
ON s.customer_id = m.customer_id
JOIN menu AS mm
ON s.product_id = mm.product_id
WHERE s.order_date < m.join_date
GROUP BY s.customer_id;

9. If each $1 spent equates to 10 points and sushi has a

2x points multiplier— how many points would each
customer have?
WITH price_points AS
(
SELECT *,
CASE
WHEN product_id = 1 THEN price * 20
ELSE price * 10
END AS points
FROM menu
)

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

09
SELECT s.customer_id, SUM(p.points) AS total_points
FROM price_points_cte AS p
JOIN sales AS s
ON p.product_id = s.product_id
GROUP BY s.customer_id

10. In the first week after a customer joins the program

(including their join date) they earn 2x points on all
items, not just sushi — how many points do customer A
and B have at the end of January?
WITH dates_cte AS
(
SELECT *,
DATEADD(DAY, 6, join_date) AS valid_date,
EOMONTH('2021-01-31') AS last_date
FROM members AS m
)
SELECT d.customer_id, s.order_date, d.join_date,
d.valid_date, d.last_date, m.product_name, m.price,
SUM(CASE
WHEN m.product_name = 'sushi' THEN 2 * 10 *
m.price
WHEN s.order_date BETWEEN d.join_date AND d.valid_date
THEN 2 * 10 * m.price
ELSE 10 * m.price
END) AS points

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

10
FROM dates_cte AS d
JOIN sales AS s
ON d.customer_id = s.customer_id
JOIN menu AS m
ON s.product_id = m.product_id
WHERE s.order_date < d.last_date
GROUP BY d.customer_id, s.order_date, d.join_date,
d.valid_date, d.last_date,
m.product_name, m.price

11)Join All The Things Recreate the table with:

customer_id, order_date, product_name, price, member
(Y/N)
SELECT s.customer_id, s.order_date, m.product_name,
m.price,
CASE
WHEN mm.join_date > s.order_date THEN 'N'
WHEN mm.join_date <= s.order_date THEN 'Y'
ELSE 'N'
END AS member
FROM sales AS s
LEFT JOIN menu AS m
ON s.product_id = m.product_id
LEFT JOIN members AS mm
ON s.customer_id = mm.customer_id;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

11
12) Rank All The Things
WITH summary_cte AS
(
SELECT s.customer_id, s.order_date, m.product_name,
m.price,
CASE
WHEN mm.join_date > s.order_date THEN 'N'
WHEN mm.join_date <= s.order_date THEN 'Y'
ELSE 'N' END AS member
FROM sales AS s
LEFT JOIN menu AS m
ON s.product_id = m.product_id
LEFT JOIN members AS mm
ON s.customer_id = mm.customer_id
)SELECT *, CASE
WHEN member = 'N' then NULL
ELSE
RANK () OVER(PARTITION BY customer_id, member
ORDER BY order_date) END AS ranking
FROM summary_cte;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

12

SQL CASE STUDY 2

The runners table shows the registration_date for each new
runner.

Customer pizza orders are captured in the customer_orders

table with 1 row for each individual pizza that is part of the
order. The pizza_id relates to the type of pizza which was
ordered whilst the exclusions are the ingredient_id values
which should be removed from the pizza and the extras are
the ingredient_id values which need to be added to the
pizza.

Note that customers can order multiple pizzas in a single

order with varying exclusionsand extras values even if the
pizza is the same type! The exclusions and extras columns will
need to be cleaned up before using them in your queries.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

13

Table : runner_orders
After each orders are received through the system - they are
assigned to a runner - however not all orders are fully
completed and can be cancelled by the restaurant or the
customer.
The pickup_time is the timestamp at which the runner arrives
at the Pizza Runner headquarters to pick up the freshly
cooked pizzas. The distance and duration fields are related to
how far and long the runner had to travel to deliver the order
to the respective customer.
There are some known data issues with this table so be
careful when using this in your queries - make sure to check
the data types for each column in the schema SQL!

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

14

Table : pizza_names
At the moment - Pizza Runner only has 2 pizzas available the
Meat Lovers or Vegetarian!

Table: pizza_recipes
Each pizza_id has a standard set of toppings which are used
as part of the pizza recipe.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

15
Table: pizza_toppings
This table contains all of the topping_name values with their
corresponding topping_id value

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

16
TRY FOLLOWING QUESTIONS

1. Perform Data Cleaning and Transformation ?

2. How many pizzas were ordered?
3. How many unique customer orders were made?
4. How many successful orders were delivered by each runner?
5. How many of each type of pizza was delivered?
6. How many Vegetarian and Meatlovers were ordered by each
customer?
7. What was the maximum number of pizzas delivered in a
single order?
8. For each customer, how many delivered pizzas had at least 1
change and how many had no changes?
9. How many pizzas were delivered that had both exclusions
and extras?
10. What was the total volume of pizzas ordered for each hour of
the day?
11. What was the volume of orders for each day of the week?
12. How many runners signed up for each 1 week period? (i.e.
week starts 2021-01-01)
13. What was the average time in minutes it took for each runner
to arrive at the Pizza Runner HQ to pickup the order?

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

17
TRY FOLLOWING QUESTIONS

14. Is there any relationship between the number of pizzas and

how long the order takes to prepare?
15. What was the average distance travelled for each customer?
16. What was the difference between the longest and shortest
delivery times for all orders?
17. What was the average speed for each runner for each
delivery and do you notice any trend for these values?
18. What is the successful delivery percentage for each runner?

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

18
1)Data Cleaning and Transformation
Firstly, to clean up exclusions and extras in the customer_orders
— we create TEMP TABLE #customer_orders and use CASE
WHEN.

SELECT order_id, customer_id, pizza_id,

CASE
WHEN exclusions IS null OR exclusions LIKE 'null' THEN ' '
ELSE exclusions
END AS exclusions,
CASE
WHEN extras IS NULL or extras LIKE 'null' THEN ' '
ELSE extras
END AS extras,
order_time
INTO #customer_orders -- create TEMP TABLE
FROM customer_orders;
Then, we clean the runner_orders table with CASE WHEN and
TRIM and create TEMP TABLE #runner_orders.

In summary,
·pickup_time — Remove nulls and replace with ‘ ‘
·distance — Remove ‘km’ and nulls
·duration — Remove ‘minutes’ and nulls
·cancellation — Remove NULL and null and replace with ‘ ‘

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

19
·SELECT order_id, runner_id,
CASE
WHEN pickup_time LIKE 'null' THEN ' '
ELSE pickup_time
END AS pickup_time,
CASE
WHEN distance LIKE 'null' THEN ' '
WHEN distance LIKE '%km' THEN TRIM('km' from distance)
ELSE distance END AS distance,
CASE
WHEN duration LIKE 'null' THEN ' '
WHEN duration LIKE '%mins' THEN TRIM('mins' from duration)
WHEN duration LIKE '%minute' THEN TRIM('minute' from
duration)
WHEN duration LIKE '%minutes' THEN TRIM('minutes' from
duration)
ELSE duration END AS duration,
CASE
WHEN cancellation IS NULL or cancellation LIKE 'null' THEN ''
ELSE cancellation END AS cancellation
INTO #runner_orders
FROM runner_orders;

Then, we alter the date according to its correct data type.

·pickup_time to DATETIME type
·distance to FLOAT type
·duration to INT type

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

20
ALTER TABLE #runner_orders
ALTER COLUMN pickup_time DATETIME,
ALTER COLUMN distance FLOAT,
ALTER COLUMN duration INT;

2)How many pizzas were ordered?

SELECT COUNT(*) AS pizza_order_count
FROM #customer_orders;

3) How many unique customer orders were made?

SELECT COUNT(DISTINCT order_id) AS unique_order_count
FROM #customer_orders;

4) How many successful orders were delivered by each

runner?
SELECT runner_id, COUNT(order_id) AS successful_orders
FROM #runner_orders
WHERE distance != 0
GROUP BY runner_id;

5) How many of each type of pizza was delivered?

SELECT p.pizza_name, COUNT(c.pizza_id) AS
delivered_pizza_count
FROM #customer_orders AS c
JOIN #runner_orders AS r
ON c.order_id = r.order_id
JOIN pizza_names AS p

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

21
ON c.pizza_id = p.pizza_id
WHERE r.distance != 0
GROUP BY p.pizza_name;

6). How many Vegetarian and Meatlovers were ordered by

each customer?
SELECT c.customer_id, p.pizza_name, COUNT(p.pizza_name) AS
order_count
FROM #customer_orders AS c
JOIN pizza_names AS p
ON c.pizza_id= p.pizza_id
GROUP BY c.customer_id, p.pizza_name
ORDER BY c.customer_id;

7)What was the maximum number of pizzas delivered in a

single order?
WITH pizza_count_cte AS
(
SELECT c.order_id, COUNT(c.pizza_id) AS pizza_per_order
FROM #customer_orders AS c
JOIN #runner_orders AS r
ON c.order_id = r.order_id
WHERE r.distance != 0
GROUP BY c.order_id
)
SELECT MAX(pizza_per_order) AS pizza_count
FROM pizza_count_cte;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

22
8) For each customer, how many delivered pizzas had at
least 1 change and how many had no changes?
SELECT c.customer_id,
SUM(CASE
WHEN c.exclusions <> ' ' OR c.extras <> ' ' THEN 1
ELSE 0
END) AS at_least_1_change,
SUM(CASE
WHEN c.exclusions = ' ' AND c.extras = ' ' THEN 1
ELSE 0
END) AS no_change
FROM #customer_orders AS c
JOIN #runner_orders AS r
ON c.order_id = r.order_id
WHERE r.distance != 0
GROUP BY c.customer_id
ORDER BY c.customer_id;

9) How many pizzas were delivered that had both

exclusions and extras?
SELECT
SUM(CASE
WHEN exclusions IS NOT NULL AND extras IS NOT NULL THEN 1
ELSE 0
END) AS pizza_count_w_exclusions_extras
FROM #customer_orders AS c

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

JOIN #runner_orders AS r
23
ON c.order_id = r.order_id
WHERE r.distance >= 1
AND exclusions <> ' '
AND extras <> ' ';

10) What was the total volume of pizzas ordered for each
hour of the day?
SELECT DATEPART(HOUR, [order_time]) AS hour_of_day,
COUNT(order_id) AS pizza_count
FROM #customer_orders
GROUP BY DATEPART(HOUR, [order_time]);

11) What was the volume of orders for each day of the
week?
SELECT FORMAT(DATEADD(DAY, 2, order_time),'dddd') AS
day_of_week,
-- add 2 to adjust 1st day of the week as Monday
COUNT(order_id) AS total_pizzas_ordered
FROM #customer_orders
GROUP BY FORMAT(DATEADD(DAY, 2, order_time),'dddd');

12)How many runners signed up for each 1 week period?

(i.e. week starts 2021-01-01)
SELECT DATEPART(WEEK, registration_date) AS registration_week,
COUNT(runner_id) AS runner_signup
FROM runners
GROUP BY DATEPART(WEEK, registration_date);

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

24
13) What was the average time in minutes it took for each
runner to arrive at the Pizza Runner HQ to pickup the
order?
WITH time_taken_cte AS
(
SELECT c.order_id, c.order_time, r.pickup_time,
DATEDIFF(MINUTE, c.order_time, r.pickup_time) AS
pickup_minutes
FROM #customer_orders AS c
JOIN #runner_orders AS r
ON c.order_id = r.order_id
WHERE r.distance != 0
GROUP BY c.order_id, c.order_time, r.pickup_time
)
SELECT AVG(pickup_minutes) AS avg_pickup_minutes
FROM time_taken_cte
WHERE pickup_minutes > 1;

14) Is there any relationship between the number of

pizzas and how long the order takes to prepare?
WITH prep_time_cte AS
(
SELECT c.order_id, COUNT(c.order_id) AS pizza_order,
c.order_time, r.pickup_time,
DATEDIFF(MINUTE, c.order_time, r.pickup_time) AS
prep_time_minutes
FROM #customer_orders AS c

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

25
JOIN #runner_orders AS r
ON c.order_id = r.order_id
WHERE r.distance != 0
GROUP BY c.order_id, c.order_time, r.pickup_time
)

SELECT pizza_order, AVG(prep_time_minutes) AS

avg_prep_time_minutes
FROM prep_time_cte
WHERE prep_time_minutes > 1
GROUP BY pizza_order;

15) What was the average distance travelled for each

customer?
SELECT c.customer_id, AVG(r.distance) AS avg_distance
FROM #customer_orders AS c
JOIN #runner_orders AS r
ON c.order_id = r.order_id
WHERE r.duration != 0
GROUP BY c.customer_id;

16) What was the difference between the longest and

shortest delivery times for all orders?
Firstly, let’s see all the durations for the orders.
SELECT order_id, duration
FROM #runner_orders
WHERE duration not like ' ';

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

26
Then, we find the difference by deducting the shortest (MIN)
from the longest (MAX) delivery times.
SELECT MAX(duration::NUMERIC) - MIN(duration::NUMERIC) AS
delivery_time_difference
FROM #runner_orders
WHERE duration not like '% %'

17) What was the average speed for each runner for each
delivery and do you notice any trend for these values?
SELECT r.runner_id, c.customer_id, c.order_id,
COUNT(c.order_id) AS pizza_count,
r.distance, (r.duration / 60) AS duration_hr ,
ROUND((r.distance/r.duration * 60), 2) AS avg_speed
FROM #runner_orders AS r
JOIN #customer_orders AS c
ON r.order_id = c.order_id
WHERE distance != 0
GROUP BY r.runner_id, c.customer_id, c.order_id, r.distance,
r.duration
ORDER BY c.order_id;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

27
18) What is the successful delivery percentage for each
runner?
SELECT runner_id,
ROUND(100 * SUM
(CASE WHEN distance = 0 THEN 0
ELSE 1
END) / COUNT(*), 0) AS success_perc
FROM #runner_orders
GROUP BY runner_id;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

28

SQL CASE STUDY 3

Users
Customers who visit the Clique Bait website are tagged via
their cookie_id.

Events
Customer visits are logged in this events table at a cookie_id
level and the event_typeand page_id values can be used to
join onto relevant satellite tables to obtain further
information about each event. The sequence_number is used
to order the events within each visit.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

29

Event Identifier
The event_identifier table shows the types of events which are
captured by Clique Bait’s digital data systems.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

30
Campaign Identifier
This table shows information for the 3 campaigns that Clique
Bait has ran on their website so far in 2020.

Page Hierarchy
This table lists all of the pages on the Clique Bait website which
are tagged and have data passing through from user
interaction events.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

31
TRY FOLLOWING QUESTIONS

1. How many users are there?

2. How many cookies does each user have on average?
3. What is the unique number of visits by all users per month?
4. What is the number of events for each event type?
5. What is the percentage of visits which have a purchase
event?
6. What is the percentage of visits which view the checkout
page but do not have a purchase event?
7. What are the top 3 pages by number of views?
8. What is the number of views and cart adds for each product
category?
9. Using a single SQL query - create a new output table which
has the following details:
How many times was each product viewed?
How many times was each product added to cart?
How many times was each product added to a cart but not
purchased (abandoned)?
How many times was each product purchased?
Additionally, create another table which further aggregates the
data for the above points but this time for each product category
instead of individual products. Use your 2 new output tables -
answer the following questions:
10.Which product had the highest view to purchase percentage?
11.What is the average conversion rate from cart add to
purchase?

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

32
1. How many users are there?
SELECT COUNT(DISTINCT user_id) AS user_count
FROM clique_bait.users;

2. How many cookies does each user have on average?

This was one of those tricky questions that seems easy, but the
solution is not as clear as it seems.
Question is asking the number of cookies each user have on
average. That's calling us to either use a DISTINCT or GROUP
BY in order to ensure the count of cookies belonging to each
user is unique.
Next, round up the average cookie count with 0 decimal
point as it will not make sense for the cookie to be in
fractional form.
WITH cookie AS (
SELECT
user_id,
COUNT(cookie_id) AS cookie_id_count
FROM clique_bait.users
GROUP BY user_id)

SELECT
ROUND(AVG(cookie_id_count),0) AS avg_cookie_id
FROM cookie;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

33
3. What is the unique number of visits by all users per
month?
First, extract numerical month from event_time so that we
can group the data by month.
Unique is a keyword to use DISTINCT.
SELECT
EXTRACT(MONTH FROM event_time) as month,
COUNT(DISTINCT visit_id) AS unique_visit_count
FROM clique_bait.events
GROUP BY EXTRACT(MONTH FROM event_time);

4. What is the number of events for each event type?

SELECT
event_type,
COUNT(*) AS event_count
FROM clique_bait.events
GROUP BY event_type
ORDER BY event_type;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

34
5. What is the percentage of visits which have a purchase
event?
Join the events and events_identifier table and filter by
Purchase event only.
As the data is now filtered to having Purchase events only,
counting the distinct visit IDs would give you the number of
purchase events.
Then, divide the number of purchase events with a subquery
of total number of distinct visits from the events table.
SELECT
100 * COUNT(DISTINCT e.visit_id)/
(SELECT COUNT(DISTINCT visit_id) FROM clique_bait.events) AS
percentage_purchase
FROM clique_bait.events AS e
JOIN clique_bait.event_identifier AS ei
ON e.event_type = ei.event_type
WHERE ei.event_name = 'Purchase';
6. What is the percentage of visits which view the
checkout page but do not have a purchase event? The
strategy to answer this question is to breakdown the question
into 2 parts.
Part 1: Create a CTE and using CASE statements, find the MAX()
of:
event_type = 1 (Page View) and page_id = 12 (Checkout), and
assign "1" to these events. These events are when user
viewed the checkout page.
event_type = 3 (Purchase) and assign "1" to these events.
These events signifies users who made a purchase.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

35
We're using MAX() because we do not want to group the results
by event_type and page_id. Since the max score is "1", it would
mean "Give me the max score for each event".
Part 2: Using the table we have created, find the percentage of
visits which view checkout page.
WITH checkout_purchase AS (
SELECT
visit_id,
MAX(CASE WHEN event_type = 1 AND page_id = 12 THEN 1 ELSE
0 END) AS checkout,
MAX(CASE WHEN event_type = 3 THEN 1 ELSE 0 END) AS
purchase
FROM clique_bait.events
GROUP BY visit_id)
SELECT
ROUND(100 * (1-(SUM(purchase)::numeric/SUM(checkout))),2)
AS percentage_checkout_view_with_no_purchase
FROM checkout_purchase

7. What are the top 3 pages by number of views?

SELECT
ph.page_name,
COUNT(*) AS page_views
FROM clique_bait.events AS e
JOIN clique_bait.page_hierarchy AS ph
ON e.page_id = ph.page_id
WHERE e.event_type = 1 -- "Page View"

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

36
GROUP BY ph.page_name
ORDER BY page_views DESC -- Order by descending to retrieve
highest to lowest number of views
LIMIT 3; -- Limit results to 3 to find the top 3

8. What is the number of views and cart adds for each

product category?
SELECT
ph.product_category,
SUM(CASE WHEN e.event_type = 1 THEN 1 ELSE 0 END) AS
page_views,
SUM(CASE WHEN e.event_type = 2 THEN 1 ELSE 0 END) AS
cart_adds
FROM clique_bait.events AS e
JOIN clique_bait.page_hierarchy AS ph
ON e.page_id = ph.page_id
WHERE ph.product_category IS NOT NULL
GROUP BY ph.product_category
ORDER BY page_views DESC;

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

37
9)Using a single SQL query - create a new output table
which has the following details:
1. How many times was each product viewed?
2. How many times was each product added to cart?
3. How many times was each product added to a cart but not
purchased (abandoned)?
4. How many times was each product purchased?
Planning Our Strategy
Let us visualize the output table.

These information would come from these 2 tables.

events table - visit_id, page_id, event_type
page_hierarchy table - page_id, product_category
Solution
Note 1 - In product_page_events CTE, find page views and
cart adds for individual visit ids by wrapping SUM around
CASE statements so that we do not have to group the results
by event_type as well.
Note 2 - In purchase_events CTE, get only visit ids that have
made purchases.

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

38
Note 3 - In combined_table CTE, merge product_page_events
and purchase_events using LEFT JOIN. Take note of the table
sequence. In order to filter for visit ids with purchases, we
use a CASE statement and where visit id is not null, it means
the visit id is a purchase.

WITH product_page_events AS ( -- Note 1

SELECT
e.visit_id,
ph.product_id,
ph.page_name AS product_name,
ph.product_category,
SUM(CASE WHEN e.event_type = 1 THEN 1 ELSE 0 END) AS
page_view, -- 1 for Page View
SUM(CASE WHEN e.event_type = 2 THEN 1 ELSE 0 END) AS
cart_add -- 2 for Add Cart
FROM clique_bait.events AS e
JOIN clique_bait.page_hierarchy AS ph
ON e.page_id = ph.page_id
WHERE product_id IS NOT NULL
GROUP BY e.visit_id, ph.product_id, ph.page_name,
ph.product_category
),
purchase_events AS ( -- Note 2
SELECT
DISTINCT visit_id
FROM clique_bait.events
WHERE event_type = 3 -- 3 for Purchase

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

39
),
combined_table AS ( -- Note 3
SELECT
ppe.visit_id,
ppe.product_id,
ppe.product_name,
ppe.product_category,
ppe.page_view,
ppe.cart_add,
CASE WHEN pe.visit_id IS NOT NULL THEN 1 ELSE 0 END AS
purchase
FROM product_page_events AS ppe
LEFT JOIN purchase_events AS pe
ON ppe.visit_id = pe.visit_id
),
product_info AS (
SELECT
product_name,
product_category,
SUM(page_view) AS views,
SUM(cart_add) AS cart_adds,
SUM(CASE WHEN cart_add = 1 AND purchase = 0 THEN 1 ELSE
0 END) AS abandoned,

zepanalytics.com
DATA ANALYTICS | SQL CASE STUDY

40
SUM(CASE WHEN cart_add = 1 AND purchase = 1 THEN 1 ELSE
0 END) AS purchases
FROM combined_table
GROUP BY product_id, product_name, product_category)
SELECT *
FROM product_info
ORDER BY product_id;

10. Which product had the highest view to purchase

percentage?
SELECT
product_name,
product_category,
ROUND(100 * purchases/views,2) AS
purchase_per_view_percentage
FROM product_info
ORDER BY purchase_per_view_percentage DESC

11. What is the average conversion rate from cart add to

purchase?
SELECT ROUND(100*AVG(cart_adds/views),2) AS
avg_view_to_cart_add_conversion,
ROUND(100*AVG(purchases/cart_adds),2) AS
avg_cart_add_to_purchases_conversion_rate
FROM product_info

zepanalytics.com
Ready to take the next steps?
So these were some of the important DAX
functions which you should remember.

Remember DAX is very vast and there are

many more but these are the basic and the
most used ones.

Become a part of the team

at Zep
Why don't you start your journey
as a tech blogger and enjoy
unlimited perks and cash prizes
every month.

Explore

zepanalytics.com