Ежедневные активные подсчеты ВЕЩЕЙ из строк, детализирующих изменение ВЕЩЕЙ

Question

Скажем, у меня есть таблица людей, которые использовали мой сервис в день N, и таблица, описывающая, на какую тему пользователи изменили. Нет таблицы с информацией о том, какую тему они сейчас используют. Я бы хотел видеть это ежедневно.

Допустим, таблица изменений выглядит так.

      | user_ID |   date   | theme |
|---------|----------|-------|
|  user1  | 1.1.2021 | Dark  |
|  user1  | 4.1.2021 | Light |
|  user2  | 2.1.2021 | Dark  |
|  user2  | 6.1.2021 | Light |

В таблице активности есть только user_ID и дата обращения к сервису.

      | user_ID |   date   |
|---------|----------|
|  user1  | 1.1.2021 |
|  user1  | 2.1.2021 |
|  user1  | 3.1.2021 |
|  user1  | 4.1.2021 |
|  user1  | 5.1.2021 |
|  user1  | 6.1.2021 |
|  user2  | 2.1.2021 |
|  user2  | 3.1.2021 |
|  user2  | 4.1.2021 |
|  user2  | 5.1.2021 |
|  user2  | 6.1.2021 |

Теперь я хотел бы присоединить первую таблицу ко второй, чтобы там была указана тема, которую они используют в активную дату.

      | user_ID |   date   | theme |
|---------|----------|-------|
|  user1  | 1.1.2021 | Dark  |
|  user1  | 2.1.2021 | Dark  |
|  user1  | 3.1.2021 | Dark  |
|  user1  | 4.1.2021 | Light |
|  user1  | 5.1.2021 | Light |
|  user1  | 6.1.2021 | Light |
|  user2  | 2.1.2021 | Dark  |
|  user2  | 3.1.2021 | Dark  |
|  user2  | 4.1.2021 | Dark  |
|  user2  | 5.1.2021 | Dark  |
|  user2  | 6.1.2021 | Light |

Как мне этого добиться? Предположим, может быть неограниченное количество тем.

Answer 1

Один из методов - это коррелированный подзапрос, но я не уверен, поддерживает ли это Presto:

      select a.*,
       (select c.theme
        from changes c
        where c.user_id = a.user_id and
              c.date <= a.date
        order by c.date desc
        limit 1
       ) as theme
from activity a;

Возможно, более эффективный метод - использовать left join но чтобы рассчитать "конечную" дату для каждого изменения:

      select a.*, c.theme
from activity a left join
     (select c.*,
             lead(date) over (partition by user_id order by date) as next_date
      from changes c
     ) c
     on a.user_id = c.user_id and
        a.date >= c.date and
        (a.date < c.next_date or c.next_date is null);