runtime
SQL Projects / Social Product Analytics

Project 02 · Social Product Analytics

Understand social engagement.

Analyze event quality, conversion funnels, sessions and experiment results.

Start project

The brief

The business question
Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions.
What you’ll deliver
Reliable events, ordered funnels, user sessions and conversion metrics.Exported as a report with business questions, checked SQL, result evidence and your written findings. Labeled Draft until every milestone has a finding.
Skills
Deduplication · Funnels · Sessions
Scope
Advanced level · 4 milestones · 3 tables

Available data

Member events, referral links, and experiment assignments. Event retries create duplicate event_key values.

How the tables connect:social_events.member_id → members.idexperiment_assignments.member_id → members.id
members5 columns · 8 rows · id, email, country, joined_at, …
  • idintegerPK
  • emailtext
  • countrytext
  • joined_attext
  • referrer_idinteger

First 5 of 8 rows · original sample data

#idemailcountryjoined_atreferrer_id
11 ALICE@Example.com US2025-01-01NULL
22ben@example.comUS2025-01-021
33 Cara@example.comGB2025-01-031
44dev@example.com IN2025-01-042
55NULLCA2025-01-05NULL
social_events6 columns · 22 rows · id, event_key, member_id, event_type, …
  • idintegerPK
  • event_keytext
  • member_idinteger
  • event_typetext
  • event_attext
  • ingested_attext

First 5 of 22 rows · original sample data

#idevent_keymember_idevent_typeevent_atingested_at
11a1view2025-01-01 10:00:002025-01-01 10:00:01
22b1signup2025-01-01 10:03:002025-01-01 10:03:01
33c1purchase2025-01-01 10:05:002025-01-01 10:05:01
44a1view2025-01-01 10:00:002025-01-01 10:10:00
55d2view2025-01-02 09:00:002025-01-02 09:00:01
experiment_assignments3 columns · 8 rows · member_id, variant, assigned_at
  • member_idintegerPK
  • varianttext
  • assigned_attext

First 5 of 8 rows · original sample data

#member_idvariantassigned_at
11control2025-01-01 09:00:00
22control2025-01-02 08:00:00
33treatment2025-01-03 08:30:00
44treatment2025-01-04 09:00:00
55control2025-01-05 10:00:00

Open a table to see its columns and sample rows. You do not need to read every table before starting a milestone.

Each run starts with the original sample data. Changes to tables do not carry over to later runs or milestones. Findings are optional for continuing — required only for a complete report.

Milestones

Start
01
Deduplicate event retries
Identify repeated events and keep a consistent record.
Not started
02
Ordered conversion funnels
Count users who complete events in the required order.
Not started
03
Sessionize an event stream
Group consecutive events into user sessions.
Not started
04
Experiment conversion denominators
Choose the correct population for experiment conversion rates.
Not started

Project report

Loading saved progress…

Waiting for saved progress to load.

Runtime — Learn it. Run it. Understand it.