I have the following tables:
users
+-------+-----------+-----------+
| id | source | age_group |
+-------+-----------+-----------+
| 1 | google | 18 - 22 |
+-------+-----------+-----------+
| 2 | facebook | 22 - 25 |
+-------+-----------+-----------+
| 3 | site | 25 - 35 |
+-------+-----------+-----------+
| 4 | google | 25 - 35 |
+-------+-----------+-----------+
| 5 | google | 18 - 22 |
+-------+-----------+-----------+
orders
+-------+-----------+
| id | userId |
+-------+-----------+
| 1 | 3 |
+-------+-----------+
| 2 | 1 |
+-------+-----------+
| 3 | 3 |
+-------+-----------+
| 4 | 2 |
+-------+-----------+
| 5 | 4 |
+-------+-----------+
| 6 | 5 |
+-------+-----------+
orders_payments:
+-------+-----------+-----------+
| id | orderId | amount |
+-------+-----------+-----------+
| 1 | 1 | 10 |
+-------+-----------+-----------+
| 2 | 2 | 0 |
+-------+-----------+-----------+
| 3 | 3 | 40 |
+-------+-----------+-----------+
| 4 | 5 | 0 |
+-------+-----------+-----------+
| 5 | 6 | 0 |
+-------+-----------+-----------+
And 3 possibilities:
I need a sales report that looks like this:
+-----------+-----------+-----------+-----------+-----------+
| source | age_group | paid | tried | no_show |
+-----------+-----------+-----------+-----------+-----------+
| google | 18 - 22 | 0 | 2 | 0 |
+-----------+-----------+-----------+-----------+-----------+
| google | 25 - 35 | 0 | 1 | 0 |
+-----------+-----------+-----------+-----------+-----------+
| facebook | 22 - 25 | 0 | 0 | 1 |
+-----------+-----------+-----------+-----------+-----------+
| site | 25 - 35 | 1 | 0 | 0 |
+-----------+-----------+-----------+-----------+-----------+
The last 3 columns represent the number of users that paid at least once, tried the services for free or didn't show up for the appointment.
The report is grouped by source and then by age_group. So something like this
SELECT source, age_group, ... FROM users GROUP BY source, age_group
I can't, for the life of me, figure it out.
Any ideas are greatly appreciated.
Thank you!
L.E.: I only want to count the users once. So if they have two paid orders I count them once as paid, if the tried but then paid I also want to count them once as paid and so on.
L.E.2: I think this is a possible solution:
select
u.source,
u.age_group,
sum(op.amount > 0) paid,
sum(op.amount = 0) tried,
sum(op.orderId is null) no_show
from users u
inner join orders o on o.userId = u.id
left join (
select max(amount) as amount, orderId
from orders_payments
left join orders on orders_payments.orderId = orders.id
group by orders.userId
) op on op.orderId = o.id
group by u.source, u.age_group
order by u.source, u.age_group
I would go for a left join
on the payments table, and then conditional aggregation.
select
u.source,
u.age_group,
sum(op.amount > 0) paid,
sum(op.amount = 0) tried,
sum(op.orderId is null) no_show
from users u
inner join orders o on o.userId = u.id
left join orders_payments op on op.orderId = o.id
group by u.source, u.age_group
order by u.source, u.age_group