Daily Spend by Payment Channel

Problem

A ride-hailing app records each payment a rider makes, tagged with the channel they paid through.

Table: payment
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| rider_id | int |
| paid_on | date |
| channel | varchar(10) |
| amount | int |
+-------------+----------+
There may be several rows per rider per day. channel is either 'app' or 'kiosk'.

Write a solution to report, for every date present in the data, the number of riders and the total amount spent across three channels: 'app', 'kiosk', and 'both'. A rider counts toward 'both' on a date when they paid through both 'app' and 'kiosk' on that same date (and they then count only toward 'both', not the individual channels). Every date must show all three channels even when a channel has no riders, in which case report 0 riders and 0 total. Return columns pay_date, channel, total_amount, and total_riders in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 6 rows with the columns pay_date, channel, total_amount, total_riders.

Starter code (SQL)

SELECT *
FROM payment;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard