gator - 2 months ago 6
MySQL Question

# Finding rows of certain conditions after rows of other conditions

To gauge the effectiveness of giving free product in getting new customers, I want to find the number of customers who have paid for an order after only receiving a free order. My table looks like:

``````id | user | total
-----------------
1    1     0.00
2    1     57.33
3    2     58.21
4    2     0.00
5    3     26.10
6    3     0.00
7    3     64.94
8    4     0.00
9    5     34.54
``````

In this table there's five customers:
`1`
,
`2`
,
`3`
,
`4`
, and
`5`
. This counts for all of the conditions I can think of.

• User 1 received a free order and then placed a paid order, this counts

• User 2 placed a paid order and then received a free order, this does not count

• User 3 placed a paid order, then received a free order, then placed a paid order, this does not count

• User 4 received a free order, this does not count

• User 5 placed a paid order, this does not count

You can see I only care about users who have not placed a paid order before receiving a free order, and then placed a paid order. A more declarative definition of the count I'm looking for is:

• Users placed an order
`n`
where
`n->total > 0`
after receiving an order
`m`
, where
`m->total == 0`
and
`m->id < n->id`
. There is no order
`w`
where
`w->total > 0`
and
`w->id < m->id`
.

Some
`create table`
code:

``````create table orders (
id int NOT NULL AUTO_INCREMENT,
user int,
total decimal(6,2),
PRIMARY KEY(id)
);

insert into orders (user, total) values
(1, 0.00),
(1, 57.33),
(2, 58.21),
(2, 0.00),
(3, 26.10),
(3, 0.00),
(3, 64.94),
(4, 0.00),
(5, 34.54);
``````

The expected output in this case would be
`1`
.

Simple aggregation combined with conditional aggregation gets you the user you are looking for.

``````SELECT
[user]
,MIN(id) as MinId
,MIN(CASE WHEN total = 0 THEN id END) as MinZeroId
,MIN(CASE WHEN total > 0 THEN id END) as MinPositiveId
FROM
orders
GROUP BY
[user]
HAVING
MIN(id) = MIN(CASE WHEN total = 0 THEN id END)
AND MIN(CASE WHEN total > 0 THEN id END) > MIN(CASE WHEN total = 0 THEN id END)
``````

That compares the minimum id for the user against the minimum id for \$0 total and then compares the minimum id for a Total > \$0 to the minimum id for \$0 Total.

And if you want the original records simply add an outer select to get to it.

``````SELECT o.*
FROM
(
SELECT
[user]
FROM
orders
GROUP BY
[user]
HAVING
MIN(id) = MIN(CASE WHEN total = 0 THEN id END)
AND MIN(CASE WHEN total > 0 THEN id END) > MIN(CASE WHEN total = 0 THEN id END)
) t
INNER JOIN orders o
ON t.[user] = o.[user]
``````