Seat Hold Payment Rate

When a cinema member picks seats online, the seats are put on hold. Each hold ends as 'paid', 'expired' (the timer ran out) or 'released' (the member gave the seats back).

For every member, compute the payment rate: the number of their holds that ended 'paid' divided by the total number of their holds. A member with no holds at all has a rate of 0.

Return member_id and payment_rate, rounded to 2 decimal places (a value between 0 and 1, not a percentage), with one row per member, in any order.

Tables

cinema_members — One row per loyalty member.

seat_holds — One row per seat hold made online.

Example

cinema_members

seat_holds

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 25 of 70 · SQLite

The idea

The rate is a share: paid holds out of all holds. The average of a flag that is 1 for paid and 0 otherwise computes exactly that, so AVG(CASE WHEN outcome = 'paid' THEN 1.0 ELSE 0.0 END) does the division for you.

Every member must be listed, so start from cinema_members and LEFT JOIN the holds. A member with no holds gets one row with NULL hold columns. NULL = 'paid' is not true, so the CASE falls through to ELSE 0.0, and that member's average is 0, which is exactly the required answer. Using 1.0 instead of 1 keeps the arithmetic in real numbers, and ROUND(..., 2) formats the result.

The alternative computes paid and total counts per member in a CTE, divides with 1.0 * to avoid integer division, and uses COALESCE(..., 0) for members the LEFT JOIN cannot match.

Solution

SELECT m.member_id,
       ROUND(AVG(CASE WHEN h.outcome = 'paid' THEN 1.0 ELSE 0.0 END), 2) AS payment_rate
FROM cinema_members AS m
LEFT JOIN seat_holds AS h
  ON h.member_id = m.member_id
GROUP BY m.member_id;

Another way

WITH per_member AS (
  SELECT member_id,
         COUNT(*) AS holds,
         SUM(outcome = 'paid') AS paid
  FROM seat_holds
  GROUP BY member_id
)
SELECT m.member_id,
       COALESCE(ROUND(1.0 * p.paid / p.holds, 2), 0) AS payment_rate
FROM cinema_members AS m
LEFT JOIN per_member AS p
  ON p.member_id = m.member_id;

Why common answers fail

Only looks at the holds table, so members who never made a hold are missing instead of showing 0.

SELECT h.member_id,
       ROUND(AVG(CASE WHEN h.outcome = 'paid' THEN 1.0 ELSE 0.0 END), 2) AS payment_rate
FROM seat_holds AS h
GROUP BY h.member_id;

Integer division truncates every partial rate to 0, and dividing by a zero count gives NULL for members with no holds.

SELECT m.member_id,
       ROUND(SUM(CASE WHEN h.outcome = 'paid' THEN 1 ELSE 0 END) / COUNT(h.hold_id), 2) AS payment_rate
FROM cinema_members AS m
LEFT JOIN seat_holds AS h ON h.member_id = m.member_id
GROUP BY m.member_id;

Leaves the no-hold case as NULL, so AVG over nothing returns NULL instead of the required 0.

SELECT m.member_id,
       ROUND(AVG(CASE WHEN h.outcome = 'paid' THEN 1.0 WHEN h.outcome IS NOT NULL THEN 0.0 END), 2) AS payment_rate
FROM cinema_members AS m
LEFT JOIN seat_holds AS h ON h.member_id = m.member_id
GROUP BY m.member_id;

What the interviewer is checking

Interviewers look for three things: keeping zero-activity entities with a LEFT JOIN, computing a ratio without integer division, and a deliberate answer for the empty case.

Mistakes to avoid

• Grouping the holds table alone and losing members with no holds.
• Integer division (SUM / COUNT) in SQLite, which truncates to 0.
• Letting the no-hold case become NULL instead of 0.

Follow-ups to expect

• Return the rate as a percentage with one decimal (ROUND(100.0 * ..., 1)).
• Only count holds from the last 30 days, still listing every member (put the date filter in the ON clause).
• Rank members by rate, but only those with at least 5 holds (HAVING COUNT(h.hold_id) >= 5).

In real work

Conversion, acceptance and success rates per user, merchant or campaign, where entities with no attempts still have to appear in the report.

Practice next