Use the following tables to answer four SQL analysis tasks. ```text rides(ride_id, ride_date, ride_rating, user_id) users(user_id, city) ``` ### Constraints & Assumptions - Write ANSI-style SQL and state any date functions that depend on your dialect. - `user_id` is unique in `users`; `ride_id` is unique in `rides`. - `ride_date` is a timestamp, and `ride_rating` may be null. - A user with no matching row in `rides` has taken zero rides. - An active month is a distinct calendar month in which a user completed at least one ride; inactive calendar months do not advance the active-month rank. ### Clarifying Questions to Ask - Is the seven-day window ending July 7 inclusive, and in which time zone is `ride_date` interpreted? - Should a null rating be ignored by the database average? - What output format should represent a calendar month? - Should cities with no qualifying users appear with a zero count? ### Part 1: Inactive Users in a Seven-Day Window Count users with zero rides during the inclusive seven-day window ending July 7, 2024. Include users who have never taken a ride. #### What This Part Should Cover - A left join or `NOT EXISTS` condition that preserves users without rides. - Date predicates placed so they do not accidentally turn an outer join into an inner join. - Clear inclusive-start and exclusive-end boundaries. ### Part 2: Monthly Ride Aggregation For each calendar month, return the total number of rides and average non-null rating rounded to two decimals. Sort months from newest to oldest. #### What This Part Should Cover - Calendar-month truncation, ride-level counting, null-aware averaging, rounding, and descending sort. ### Part 3: Low-Frequency Users by City For each city, count distinct users who have taken zero or one ride across all available history. Include users who have never taken a ride. #### What This Part Should Cover - A user-level ride count built without losing zero-ride users, followed by a city-level aggregation. - Protection against counting rides rather than qualifying users. ### Part 4: Ratings in the First and Third Active Months For each city, compute the average ride rating separately for users' first and third active months. The final average must be across all qualifying rides, not an average of user-level or month-level averages. #### What This Part Should Cover - Deduplication to one user-month before ranking active months. - A per-user chronological rank that skips inactive calendar months. - A join back to ride-level data before calculating the final weighted average. ### What a Strong Answer Covers - Correct preservation of zero-activity users, explicit date boundaries, and the appropriate aggregation grain for every part. - Readable CTEs and joins whose cardinality can be explained. - Awareness of null ratings, time zones, ties at the month grain, and SQL-dialect differences. ### Follow-up Questions 1. How would you return zero for a city with no inactive users in Part 1? 2. What index would help the date-window query? 3. Why is averaging per-user averages incorrect in Part 4? 4. How would you adapt Part 4 to compare first and third active months in separate columns?