First Sign-Ins by Day

Problem

A streaming service logs each action a viewer takes, including their sign-ins.

Table: viewer_event
+-------------+------+
| Column Name | Type |
+-------------+------+
| viewer_id | int |
| action | text |
| acted_on | date |
+-------------+------+
There may be many rows per viewer. Each row records one action a viewer took on a given date. The action column holds values such as 'signin', 'browse', and 'signout'.

Write a solution to report, for each date within the last 90 days, how many viewers signed in for the very first time on that date. A viewer's first sign-in is the earliest date on which they have a 'signin' action. Assume today is 2023-06-30, so only first sign-ins on or after 2023-04-01 should appear. Return columns first_signin_date and new_viewers in any order. Only include dates that had at least one first-time sign-in.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns first_signin_date, new_viewers.

Starter code (SQL)

SELECT *
FROM viewer_event;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium