I am trying to create a new row for each customer who spends per day, and a column that indicates whether they spent money the day before or not. If a customer spends twice a day, they'd still only have 1 row in the table. If the customer spent money the previous day, then it would show up as TRUE.
This is the original table below:
+---------------------+-------------+-----------------+
| datetime | customer_id | amount |
+---------------------+-------------+-----------------+
| 2018-03-01 03:00:00 | 3786 | 14.00000 |
| 2018-03-02 17:00:00 | 5678 | 25.00000 |
| 2018-07-09 18:00:00 | 5647 | 1000.99000 |
| 2018-08-17 19:00:00 | 5267 | 45.00000 |
| 2018-08-25 08:00:00 | 3456 | 78.00000 |
| 2018-08-25 17:00:00 | 3456 | 25.00000 |
| 2018-08-26 03:00:00 | 3456 | 34.90000 |
| 2019-02-03 08:00:00 | 3468 | 0.00000 |
| 2019-03-09 06:00:00 | 1111 | 100.00000 |
| 2019-05-25 14:00:00 | 3456 | 15.00000 |
| 2019-07-02 14:00:00 | 88889 | 45.00000 |
| 2019-07-04 03:00:00 | 8979 | 9.00000 |
| 2019-07-09 14:00:00 | 4567 | 9.99000 |
| 2019-08-25 08:00:00 | 1234 | 88.00000 |
| 2019-08-30 09:31:00 | 1234 | 30.00000 |
| 2019-08-30 12:00:00 | 9876 | 55.00000 |
| 2019-09-01 13:00:00 | 88889 | 23.00000 |
+---------------------+-------------+-----------------+
This is the CREATE statement:
CREATE TABLE IF NOT EXISTS `spend` ( `datetime` datetime NOT NULL, `customer_id` int(11) NOT NULL, `amount` decimal(10, 5) NOT NULL, PRIMARY KEY (`datetime`)) DEFAULT CHARSET=utf8mb4;
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-01 03:00:00', 3786, 14.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-02 17:00:00', 5678, 25.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-07-09 18:00:00', 5647, 1000.99000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-17 19:00:00', 5267, 45.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 08:00:00', 3456, 78.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 17:00:00', 3456, 25.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-26 03:00:00', 3456, 34.90000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-02-03 08:00:00', 3468, 0.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-03-09 06:00:00', 1111, 100.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-05-25 14:00:00', 3456, 15.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-07-02 14:00:00', 88889, 45.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-07-04 03:00:00', 8979, 9.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-07-09 14:00:00', 4567, 9.99000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-25 08:00:00', 1234, 88.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 09:31:00', 1234, 30.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 12:00:00', 9876, 55.00000);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-09-01 13:00:00', 88889, 23.00000);Here's what I've got so far:
SELECT CAST(datetime AS DATE) AS day,
COUNT(DISTINCT customer_id) AS daily_spend,
FROM spend
WHERE amount is not NULL
ORDER BY date;This code is not working at the moment, but I'm trying to fix it as best I can.
I've had a look through some of the posts, but the closest I could find was this: count transaction per day
I'm trying to produce a table that looks like this:
+------------+-------------+--------------------+
| day | customer_id | spent_previous_day |
+------------+-------------+--------------------+
| 2018-03-01 | 3786 | FALSE |
+------------+-------------+--------------------+
| 2018-03-02 | 5678 | FALSE |
+------------+-------------+--------------------+
| 2018-07-09 | 5647 | FALSE |
+------------+-------------+--------------------+
| 2018-08-17 | 5267 | FALSE |
+------------+-------------+--------------------+
| 2018-08-25 | 3456 | FALSE |
+------------+-------------+--------------------+
| 2018-08-26 | 3456 | TRUE |
+------------+-------------+--------------------+
| 2019-02-03 | 3468 | FALSE |
+------------+-------------+--------------------+
| 2019-03-09 | 1111 | FALSE |
+------------+-------------+--------------------+
| 2019-05-25 | 3456 | FALSE |
+------------+-------------+--------------------+
| 2019-07-02 | 88889 | FALSE |
+------------+-------------+--------------------+
| 2019-07-04 | 8979 | FALSE |
+------------+-------------+--------------------+
| 2019-07-09 | 4567 | FALSE |
+------------+-------------+--------------------+
| 2019-08-25 | 1234 | FALSE |
+------------+-------------+--------------------+
| 2019-08-30 | 1234 | FALSE |
+------------+-------------+--------------------+
| 2019-08-30 | 9876 | FALSE |
+------------+-------------+--------------------+
| 2019-09-01 | 88889 | FALSE |
+------------+-------------+--------------------+
Edit: This is the current code I'm using, based on the advice I've received.
select customer_id, CAST(datetime AS DATE) AS day,
max(date(datetime)) over (partition by customer_id
order by CAST(datetime AS DATE)
range between interval 1 day preceding and interval 1 day preceding
) is not null AS spent_previous_day
from spendThis is the resulting table:
+------------+-------------+--------------------+
| day | customer_id | spent_previous_day |
+------------+-------------+--------------------+
| 2019-03-09 | 1111 | 0 |
+------------+-------------+--------------------+
| 2019-08-25 | 1234 | 0 |
+------------+-------------+--------------------+
| 2019-08-30 | 1234 | 0 |
+------------+-------------+--------------------+
| 2018-08-25 | 3456 | 0 |
+------------+-------------+--------------------+
| 2018-08-25 | 3456 | 0 |
+------------+-------------+--------------------+
| 2018-08-26 | 3456 | 1 |
+------------+-------------+--------------------+
| 2019-05-25 | 3456 | 0 |
+------------+-------------+--------------------+
| 2019-02-03 | 3468 | 0 |
+------------+-------------+--------------------+
I've tried to do a GROUP BY day, customer_id, but it comes up as an error.
Assuming that customers do not make multiple purchases on the same day, just use lag():
select t.*,
( date(lag(datetime) over (partition by customer_id order by datetime)) = date(datetime) - interval 1 day
) as prev_day_flag
from spend t;
If you can have duplicates, then try this instead of lag():
max(date(datetime)) over (partition by customer_id
order by date(datetime)
range between interval 1 day preceding and interval 1 day preceding
) is not null
EDIT:
If you want one row per customer per day:
select s.*,
( date(lag(dte) over (partition by customer_id order by dte)) = dte - interval 1 day
) as prev_day_flag
from (select customer_id, date(datetime) as dte, sum(amount) as amount
from spend s
group by customer_id, date(datetime)
) s;