Non-US Countries With at Least 1,000 Video-Watch Events in a Date Range

A company that runs several video apps logs user activity in an event table. For a reporting period, find every country other than the United States in which users generated at least 1,000 video-watch events during that period. The report gives the reporting period only as a specified date range. This version fixes it to 2025-06-01 through 2025-06-07, both dates included, and fixes how events and countries are stored as described below. ### Input Table(s) **`daily_log`**: one row per app event. The table has no primary key; rows that are identical in every column are separate events, and each one counts. | Column | Type | Meaning | | --- | --- | --- | | `date` | DATE | Calendar date of the event | | `user_id` | INTEGER | User who generated the event | | `app_id` | INTEGER | App in which the event happened | | `country` | TEXT | Two-letter uppercase code of the country where the event happened; `'US'` is the United States | | `event_type` | TEXT | Kind of event; `'video_watch'` marks a video-watch event, and other values such as `'like'` or `'share'` are other events | ### Output Contract Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per qualifying country, with these columns in this order: | Column | Type | Meaning | | --- | --- | --- | | `country` | text | Country code | | `video_watch_events` | integer | Number of video-watch events in that country from 2025-06-01 through 2025-06-07 | A country qualifies when its code is not `'US'` and its `video_watch_events` is at least 1,000. Order the rows by `video_watch_events` descending, then by `country` ascending. ### Example To keep the example short, each line below stands for several identical rows of `daily_log`. The `copies` column gives how many and is not a column of the table. `daily_log`: | date | user_id | app_id | country | event_type | copies | | --- | --- | --- | --- | --- | --- | | 2025-06-02 | 101 | 1 | BR | video_watch | 600 | | 2025-06-05 | 102 | 2 | BR | video_watch | 400 | | 2025-06-03 | 201 | 1 | US | video_watch | 1500 | | 2025-06-04 | 301 | 1 | MX | video_watch | 999 | | 2025-06-08 | 302 | 1 | MX | video_watch | 50 | | 2025-06-01 | 401 | 2 | ID | video_watch | 1200 | | 2025-06-07 | 402 | 1 | ID | like | 300 | | 2025-05-31 | 501 | 1 | JP | video_watch | 2000 | | 2025-06-07 | 502 | 2 | JP | video_watch | 10 | Expected result: | country | video_watch_events | | --- | --- | | ID | 1200 | | BR | 1000 | - ID: 1,200 video-watch events on 2025-06-01, the first day of the period, so it qualifies. Its 300 `like` events do not count. - BR: 600 + 400 = 1,000 video-watch events inside the period. That is exactly the threshold, so BR qualifies, even though the events come from two users and two apps. - MX: 999 video-watch events inside the period. The 50 on 2025-06-08 fall after it, so MX does not qualify. - JP: only 10 video-watch events inside the period. The 2,000 on 2025-05-31 fall before it. - US: 1,500 video-watch events, but the United States is excluded. ### Constraints and Clarifications - No column contains NULL. - Both ends of the period are included; events on any other date are ignored. - Only rows with `event_type = 'video_watch'` count. The threshold applies to the number of events, not to distinct users, and events from all apps and users in a country are combined. - "At least 1,000" includes a country with exactly 1,000 events. - Country codes are compared exactly as stored: `'US'` is the only code for the United States, and every other code is a non-US country. - Countries with no video-watch events in the period do not appear. If no country qualifies, the result is empty. ```hint Two kinds of conditions Some conditions apply to individual events and one applies to a country's total; decide where in the query each one belongs. ```