Amazon sql question regarding customer table and no. of days

Question

A similar type of sql question was asked in Amazon. Any help would be greatly appreciated.

How many days a customer has used amazon prime per every month.

Table name - customer

Input -

 id |  start_dt  |   end_dt
----+------------+------------
  1 | 2021-01-10 | 2021-03-05
  2 | 2021-01-15 | 2021-02-10
  1 | 2021-03-21 | 2021-03-25
  2 | 2021-02-20 | 2021-02-25

Output -

month id no_of_days
Jan     1  21
Feb     1  28
Mar     1  9
Jan     2  16
Feb     2  15
Mar     2  Null

Thanks.

Answer 1

Since it is Amazon, I believe it would be MySQL related thing. Hope this SQL will give you some general ideas.

create table Test(id integer, start_dt varchar(100), end_dt varchar(100));
insert into Test(id, start_dt, end_dt) values(1, "2021-01-10", "2021-03-05");
insert into Test(id, start_dt, end_dt) values(2, "2021-01-15", "2021-02-10");
insert into Test(id, start_dt, end_dt) values(1, "2021-03-21", "2021-03-25");
insert into Test(id, start_dt, end_dt) values(2, "2021-02-20", "2021-02-25");


SELECT DATE_FORMAT(result.days, "%b") 'month', result.id, SUM(DATE_FORMAT(result.days, "%d")) days
FROM (
SELECT Test.id,
IF(DATE_FORMAT(Test.start_dt, "%Y-%m") = tmp.month AND DATE_FORMAT(Test.end_dt, "%Y-%m") = tmp.month, CONCAT(DATE_FORMAT(Test.start_dt, "%Y-%m-"), DATEDIFF(Test.end_dt, Test.start_dt) + 1),
    IF(DATE_FORMAT(Test.start_dt, "%Y-%m") = tmp.month, Test.start_dt, 
        IF(DATE_FORMAT(Test.end_dt, "%Y-%m") = tmp.month, Test.end_dt, 
            DATE_ADD(DATE_ADD(CONCAT(tmp.month, '-01'), INTERVAL 1 MONTH), INTERVAL -1 DAY))))  days
FROM Test
CROSS JOIN
(
    select 
      DATE_FORMAT(m1, "%Y-%m") 'month'
    from
     (
      select 
       '2021-01-01' + INTERVAL m MONTH as m1
      from
       (
         select
           @rownum:=@rownum+1 as m
         from
          (select 1 union select 2 union select 3 union select 4) t1,
          (select 1 union select 2 union select 3 union select 4) t2,
          (select 1 union select 2 union select 3 union select 4) t3,
          (select 1 union select 2 union select 3 union select 4) t4,
          (select @rownum:=-1) t0
       ) d1
     ) d2 
    where
      m1 <= '2021-04-01'
    order by m1
) tmp
WHERE tmp.month >= DATE_FORMAT(Test.start_dt, "%Y-%m") AND tmp.month <= DATE_FORMAT(Test.end_dt, "%Y-%m") 
)result
GROUP BY result.id, DATE_FORMAT(result.days, "%b")

Link: https://paiza.io/projects/CIZwnoCIXKI2EbjCRGj5hw