holidays.sql

holidays.sql

SELECT user_id,
       last_date,
       spent
FROM
  (SELECT wa.user_id,
          MAX(wa.created_on) AS last_date
   FROM ec4u.wfh_answer wa
   GROUP BY wa.user_id) users
LEFT JOIN LATERAL
  (SELECT count(*) spent
   FROM generate_series(users.last_date, '2020-11-30'::TIMESTAMP, '1 day'::interval) dd
   LEFT JOIN ec4u.holiday h ON h.holiday_date = dd
   WHERE to_char(date_trunc('day', dd)::date, 'day') not in ('saturday ', 'sunday   ')
     AND holiday_date IS NULL ) days ON TRUE;