Say I have a table like this:
http://sqlfiddle.com/#!9/800cb5
CREATE TABLE IF NOT EXISTS `transactions` (
`user_id` int(6) unsigned NOT NULL,
`created_at` datetime NOT NULL,
`bow` int(6) NOT NULL,
`eow` int(6) NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`user_id`, `created_at`, `bow`, `eow`) VALUES
('1', '2019-01-01', '0', '1'),
('1', '2019-01-02', '1', '1'),
('1', '2019-01-03', '1', '1'),
('1', '2019-01-05', '1', '0'),
('2', '2019-01-11', '1', '1'),
('1', '2019-02-01', '0', '1'),
('1', '2019-02-02', '1', '1'),
('1', '2019-02-03', '1', '0');
If the "bow" is equal to 0 when the "eow" is equal to 1, I want to start counting days. I want to stop counting when "bow" equals 1 and "eow" equals 0, then take the number of days between these two values.
So my output would be:
(`user_id`, `created_at`, `days`)
('1', '2019-01-05', '4')
('1', '2019-02-03', '2')
as there are 4 days between 2019-01-01 and 2019-01-05 and 2 days between 2019-02-01 and 2019-02-03.
You can use window functions -- specifically a cumulative conditional max() to get the previous date.
Then just filter for where the period ends:
select t.*,
datediff(created_at, prev_created_at)
from (select t.*,
max(case when bow = 0 and eow = 1 then created_at end) over (order by created_at) as prev_created_at
from transactions t
) t
where bow = 1 and eow = 0;
Here is a db<>fiddle.