Как сделать задачу одним запросом в MySQL?

Question

[KirSupi]

Задача такая:
Есть табличка subscribers, в ней подписчики (subscriber_id, joined_date, left_date)
joined_date и left_date - даты подписки и отписки по датам соответственно
left_date может быть NULL (если человек в данный момент подписан)

Надо одним запросом получить кол-во подписок и отписок за каждый день в диапазоне

Таблица для примера:
+---------------+-------------+------------+
| subscriber_id | joined_date | left_date  |
+---------------+-------------+------------+
| 1             | 2022-03-31  | NULL       |
+---------------+-------------+------------+
| 2             | 2022-03-31  | 2022-04-02 |
+---------------+-------------+------------+
| 3             | 2022-04-01  | NULL       |
+---------------+-------------+------------+
| 4             | 2022-04-02  | 2022-04-02 |
+---------------+-------------+------------+
| 5             | 2022-04-02  | NULL       |
+---------------+-------------+------------+
| 6             | 2022-04-02  | NULL       |
+---------------+-------------+------------+

Если мы делаем запрос с "2022-03-31" по "2022-04-02", то результат должен быть такой:
+------------+--------------+------------+
| date       | joined_count | left_count |
+------------+--------------+------------+
| 2022-03-31 | 2            | 0          |
+------------+--------------+------------+
| 2022-04-01 | 1            | 0          |
+------------+--------------+------------+
| 2022-04-02 | 3            | 2          |
+------------+--------------+------------+

Answer 1

[Rsa97]

MySQL 8.0 и выше

WITH RECURSIVE `cte` (`date`) AS (
  SELECT :from_date
  UNION
  SELECT `date` + INTERVAL 1 DAY
    FROM `cte`
    WHERE `date` < :to_date
)
SELECT `cte`.`date`,
       IFNULL(`s`.`count`, 0) AS `joined_count`,
       IFNULL(`u`.`count`, 0) AS `left_count`
  FROM `cte`
  LEFT JOIN (
    SELECT `joined_date`, COUNT(*) AS `count`
      FROM `table`
      WHERE `joined_date` BETWEEN :from_date AND :to_date
      GROUP BY `joined_date`
  ) AS `s` ON `s`.`joined_date` = `cte`.`date`
  LEFT JOIN (
    SELECT `left_date`, COUNT(*) AS `count`
      FROM `table`
      WHERE `left_date` BETWEEN :from_date AND :to_date
      GROUP BY `left_date`
  ) AS `u` ON `u`.`left_date` = `cte`.`date`

Answer 2

[Ильдар Гизетдинов]

SELECT date, SUM(joined_count) as joined_count, SUM(left_count) as left_count FROM (
	SELECT joined_date as date, COUNT(1) as joined_count, 0 as left_count FROM table
	WHERE joined_date BETWEEN (2022-03-31, 2022-04-02)
	GROUP BY DAY(joined_date), MONTH(joined_date), YEAR(joined_date)

	UNION ALL

	SELECT left_date as date, 0 as joined_count, COUNT(1) as left_count FROM table
	WHERE left_date BETWEEN (2022-03-31, 2022-04-02)
	GROUP BY DAY(left_date), MONTH(left_date), YEAR(left_date)
)
GROUP BY date