Same-Day Session and Transaction Correlation

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Walmart

Problem

Show how many separate days each customer visited the site, how many purchases they made, and their average purchase value. The growth team's browse-and-buy profile covers only customers who appear in both user_sessions and transactions.

Same day session and purchase. Connected?

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this really is

Strip the label and this is the intersection of two per-user event streams: the people who show up in both the browsing log and the buying log. The INNER JOIN is the load-bearing move, and the moment you join user_sessions to transactions on user_id, every purchase repeats once for each session that user opened. A plain COUNT(t.transaction_id) then counts session-purchase pairs, not purchases; the query still runs clean, so the wrong numbers ship quietly.

Building it

Step 1: Inner join to keep only browsers who bought

INNER JOIN user_sessions to transactions on user_id keeps only the users present in both streams; browse-only users and buy-only users drop out. That is the 'both browse and buy' requirement expressed as a join.

Step 2: Fold same-day sessions into calendar days

session_start is a timestamp carrying a clock time, so several sessions in one day are several rows. Wrap date() and count distinct: COUNT(DISTINCT date(s.session_start)). Without date() you count instants; without DISTINCT you count the fanned rows. User 100 opened 8 sessions across 6 days and must report 6.

Step 3: Count purchases duplicate-safe, average the money

The join fans each purchase out once per session, so use COUNT(DISTINCT t.transaction_id). For spend, AVG(t.total_amount) is safe because every purchase repeats the same number of times. Leave it unrounded: several users average exactly half a cent (user 100 lands on 717.165), and rounding that to cents flips on floating-point noise. A SUM would be multiplied by the session count.

Naive: count after the join

COUNT(t.transaction_id) over the joined rows is multiplied by the sessions per user, so user 100 reports 96 purchases instead of 12.

Correct: distinct counts and an average

COUNT(DISTINCT t.transaction_id) and COUNT(DISTINCT date(s.session_start)) recover the true counts, and AVG(t.total_amount) is invariant under a uniform fan-out.

The solution

SELECT s.user_id,
       COUNT(DISTINCT date(s.session_start)) AS active_days,
       COUNT(DISTINCT t.transaction_id) AS total_transactions,
       AVG(t.total_amount) AS avg_purchase_value
FROM user_sessions s
INNER JOIN transactions t ON s.user_id = t.user_id
GROUP BY s.user_id

Common follow-up questions

• A user has 5 sessions across 2 days and 3 purchases. After the join, how many rows do they contribute, and what are `active_days`, `total_transactions` and `avg_purchase_value`? (Tests whether they understand the fan-out arithmetic, not just having memorized 'add DISTINCT'.)
• How would you also report each user's true total spend, not just the average? (Forces the realization that a correct `SUM` needs `transactions` aggregated before the join.)