SQL Queries for Monthly Counts | Dataford Interview Questions

Your question is SQL Queries for Monthly Counts. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Evidation Health uses member activity records to monitor engagement in the Evidation platform. Analyze activity recorded in Seattle for members whose participation is currently consented.

Write a PostgreSQL query that produces monthly Seattle activity counts and identifies the month or months with the highest count.

Requirements

Join activity records to participant consent information and the city reference table.
Include only records for Seattle, consented participants, and non-null recording dates.
Return one row per month with the month formatted as YYYY-MM and the activity count.
Include the prior month's count, the month-over-month change, and a flag identifying peak month ties.
Use PostgreSQL syntax and state that the query is written for PostgreSQL.

Schema

activity_events

Column	Type	Description
event_idPK	INTEGER	Activity record identifier
user_id	INTEGER	Evidation member identifier
recorded_on	DATE	Date the activity was recorded
city	VARCHAR(80)	City associated with the activity

participants

Column	Type	Description
user_idPK	INTEGER	Member identifier
consent_status	VARCHAR(20)	Current participation consent status

city_reference

Column	Type	Description
city_namePK	VARCHAR(80)	Canonical city name
region	VARCHAR(80)	State or region