gator gator - 3 months ago 8
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
.

Answer

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]