My Blog

WITH recursive months AS (
  SELECT 
    1 AS month 
  UNION ALL 
  SELECT 
    month + 1 
  FROM 
    months 
  WHERE 
    month < 12
) 
SELECT 
  m.month AS month, 
  ifnull(
    round(
      (
        count(
          DISTINCT(t.driver_id)
        ) / (
          SELECT 
            count(driver_id) 
          FROM 
            drivers 
          WHERE 
            join_date < date_add(
              '2020-1-1', INTERVAL m.month month
            )
        ) * 100
      ), 
      2
    ), 
    0.00
  ) working_percentage 
FROM 
  (
    SELECT 
      r.ride_id, 
      r.requested_at requested_date, 
      ar.ride_id accepted_ride, 
      ar.driver_id 
    FROM 
      rides r 
      RIGHT JOIN acceptedrides ar ON r.ride_id = ar.ride_id 
    WHERE 
      r.requested_at < '2021-01-01' 
      AND r.requested_at > '2019-12-31' 
    ORDER BY 
      r.requested_at
  ) t 
  RIGHT JOIN months m ON m.month = month(t.requested_date) 
GROUP BY 
  m.month;

select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) p
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

select (select subdate(t2.login_date, 1)) as date,
round(t2.cnt/(select count(user_id) from login where date=(select subdate(t2.login_date, 1)) and (user_id, (select subdate(t2.login_date, 1))) in (select user_id, min(date) as day1 from login group by user_id) group by date), 3) as p from
(select t.login_date, count(t.2ndlogin_diff) cnt from
(select user_id, date as login_date, datediff(nth_value(date, 2) over(partition by user_id order by date),nth_value(date, 1) over(partition by user_id order by date)) as 2ndlogin_diff
from login order by date) t
where t.2ndlogin_diff=1
group by t.login_date
) t2
union
select date, 0.000 p from login where date not in (select min(date) as day1 from login group by user_id)
union
select l.date, 0.000 p from login l where (select date_add(l.date, interval 1 day)) not in (select date from login)
order by date;