8 Week SQL Challenge - Case Study 6 (Digital Analysis)

We are back on track and in the article we answer the questions for the 2. Digital Analysis focus area in the 6th case study: Case Study #6 - Clique Bait

How many users are there?

SELECT 
	COUNT(DISTINCT user_id) as total_num_users
FROM users;

How many cookies does each user have on average?

SELECT
	ROUND(COUNT(cookie_id) / COUNT(DISTINCT user_id), 2) AS avg_cookies_per_user
FROM users;

What is the unique number of visits by all users per month?

SELECT
	EXTRACT(MONTH FROM event_time) AS months,
    COUNT(DISTINCT visit_id) AS unique_events
FROM events
GROUP BY months
ORDER BY unique_events DESC;

What is the number of events for each event type?

SELECT
	event_identifier.event_name AS event_name,
    COUNT(events.event_type) AS total_events
FROM
events
LEFT JOIN event_identifier
ON events.event_type = event_identifier.event_type
GROUP BY event_identifier.event_name;

What is the percentage of visits which have a purchase event?

What is the percentage of visits which have a purchase event?
SELECT
	ROUND(100 * 
		COUNT(DISTINCT CASE WHEN event_type = 3 THEN visit_id END)
        / COUNT(DISTINCT visit_id), 
	2) AS purchase_percentage
FROM events;