SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 249 of 250

Hard

Measure purchase conversion after a first product view

Your task

For each signup source with at least one product viewer, return source, viewers, converted_viewers, and conversion_pct. A viewer converts if they purchase after their first view and before seven days from that view. Compare timestamp then event ID for the lower bound. Round the percentage to one decimal and sort by source.

A customer-level conversion can span several sessions. Each viewer should contribute at most once to each count.

Sample data

Choose a table
idnamesignup_datesource
1Avery2025-01-02organic
2Casey2025-01-05email
3Jordan2025-01-12organic
4Morgan2025-02-01email
5Riley2025-02-10social
6Sam2025-02-20organic
7Taylor2025-03-01social
8Devon2025-02-07email

Expected output

sourceviewersconverted_viewersconversion_pct
email33100
organic3133.3
social100
Show a hint

Rank product views per customer, then test for a qualifying purchase with EXISTS before aggregating by source.

Product events and funnels guide

Choose the event, session, or customer grain

An event is one logged action. A session is a group of events with the same session_key. A customer can have several sessions. Choose the grain before counting so a busy visitor cannot accidentally count as many people.

SELECT event_name, COUNT(*) AS events, COUNT(DISTINCT session_key) AS sessions
FROM product_events
GROUP BY event_name
ORDER BY event_name;

Order events before evaluating a funnel

A funnel measures steps completed in a defined order. Events in the same session can share an exact timestamp, so use the event ID as a tie-breaker. A session with a purchase before the cart event does not meet a view, cart, purchase funnel.

SELECT session_key, event_name,
  ROW_NUMBER() OVER (PARTITION BY session_key ORDER BY event_at, id) AS position
FROM product_events
ORDER BY session_key, position;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…