Museum Visit Rollup

A museum keeps older exhibit-visit totals in an archive while recording the current week's visits as individual entry events.

Table: visit_archive

+--------------+-------------+
| Column Name  | Type        |
+--------------+-------------+
| archive_id   | integer     |
| visitor_id   | integer     |
| exhibit_id   | integer     |
| visit_count  | integer     |
+--------------+-------------+
+--------------+-------------+
| Column Name  | Type        |
+--------------+-------------+
| archive_id   | integer     |
| visitor_id   | integer     |
| exhibit_id   | integer     |
| visit_count  | integer     |
+--------------+-------------+

Each row stores a pre-aggregated number of visits for one visitor and exhibit through May 31, 2026. More than one archive row may exist for the same visitor-exhibit pair.

Table: gallery_visits

+-------------+-----------+
| Column Name | Type      |
+-------------+-----------+
| visitor_id  | integer   |
| exhibit_id  | integer   |
| visited_at  | timestamp |
+-------------+-----------+
+-------------+-----------+
| Column Name | Type      |
+-------------+-----------+
| visitor_id  | integer   |
| exhibit_id  | integer   |
| visited_at  | timestamp |
+-------------+-----------+

Each row represents one exhibit visit during June 1 through June 7, 2026.

Calculate every visitor's cumulative visit count for each exhibit through the end of June 4, 2026. Combine all archived counts with individual gallery visits that occurred before June 5 at midnight. Each qualifying row in gallery_visits contributes one visit.

Return visitor_id, exhibit_id, and visit_count. Order the results by visit_count from highest to lowest, then by visitor_id and exhibit_id from lowest to highest.

Output

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| visitor_id   | integer |
| exhibit_id   | integer |
| visit_count  | integer |
+--------------+---------+
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| visitor_id   | integer |
| exhibit_id   | integer |
| visit_count  | integer |
+--------------+---------+

Example

Input: visit_archive

+------------+------------+------------+-------------+
| archive_id | visitor_id | exhibit_id | visit_count |
+------------+------------+------------+-------------+
| 10011      | 777        | 1238       | 11          |
| 12452      | 695        | 4520       | 1           |
+------------+------------+------------+-------------+
+------------+------------+------------+-------------+
| archive_id | visitor_id | exhibit_id | visit_count |
+------------+------------+------------+-------------+
| 10011      | 777        | 1238       | 11          |
| 12452      | 695        | 4520       | 1           |
+------------+------------+------------+-------------+

Input: gallery_visits

+------------+------------+---------------------+
| visitor_id | exhibit_id | visited_at          |
+------------+------------+---------------------+
| 777        | 1238       | 2026-06-01 12:00:00 |
| 695        | 4520       | 2026-06-04 08:00:00 |
| 125        | 9630       | 2026-06-04 16:00:00 |
| 695        | 9852       | 2026-06-07 12:00:00 |
+------------+------------+---------------------+
+------------+------------+---------------------+
| visitor_id | exhibit_id | visited_at          |
+------------+------------+---------------------+
| 777        | 1238       | 2026-06-01 12:00:00 |
| 695        | 4520       | 2026-06-04 08:00:00 |
| 125        | 9630       | 2026-06-04 16:00:00 |
| 695        | 9852       | 2026-06-07 12:00:00 |
+------------+------------+---------------------+

Output:

+------------+------------+-------------+
| visitor_id | exhibit_id | visit_count |
+------------+------------+-------------+
| 777        | 1238       | 12          |
| 695        | 4520       | 2           |
| 125        | 9630       | 1           |
+------------+------------+-------------+
+------------+------------+-------------+
| visitor_id | exhibit_id | visit_count |
+------------+------------+-------------+
| 777        | 1238       | 12          |
| 695        | 4520       | 2           |
| 125        | 9630       | 1           |
+------------+------------+-------------+

Explanation: Visitor 777 has eleven archived visits to exhibit 1238 plus one June 1 visit. Visitor 695 has one archived visit to exhibit 4520 plus one June 4 visit. Visitor 125 appears only in the current-week events. The June 7 visit occurs after the cutoff and is excluded entirely.