SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table segment (date date, ClientID varchar(6),SegmentID int); insert into segment values ('2018-01-31' ,'A11111', 2), ('2018-02-28' ,'A11111', 2), ('2018-03-31' ,'A11111', 1), ('2018-04-30' ,'A11111', 1), ('2017-11-30' ,'B22222', 1), ('2017-10-31' ,'B22222', 1), ('2017-09-30' ,'B22222', 3), ('2017-09-30' ,'C33333', 1), ('2017-10-31' ,'C33333', 1); WITH tbl AS (SELECT DATE_SUB(date, INTERVAL DAY(date)-1 DAY) AS first_m_day, date AS last_m_date, ClientID, SegmentID, DATEDIFF(date,DATE_SUB(date, INTERVAL DAY(date) DAY)) AS cnt_days FROM segment) SELECT ClientID, SegmentID, SUM(cnt_days) AS segment_interval FROM tbl GROUP BY ClientID, SegmentID
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear