Loading this exercise…

Task 1: Identify the Peak Infection Day in Each City

#1 · 1 of 8Free sample

Instructions

Task 1: Identify the Peak Infection Day in Each City

Find the date with the highest number of infections for each city. Using the cities and infection_stats tables, join the two tables on city_id and, for each city, select the date at which the new_infections reached its maximum. Return the following columns: city_id, city_name, date, and new_infections.

Loading the practice editor and dataset…

Sign up to unlock

The reference answer is one attempt away.

Create a free account to save your progress and reveal the worked answer for this free exercise. The complete practice library is available with Practice Pass or Mastery.

Save attempts and completed exercises
Reveal this free exercise's answer after an attempt
Return to your progress from any device
Free forever — no credit card

Create free account →Already a member? Log in →

jNext →

Task 2: Identify City Leaders With Dangerous Trends

Tables

4 tables · 17 columns

cities3 cols

city_idINTEGER PRIMARY KEY
city_nameTEXT NOT NULL
populationINTEGER NOT NULL

infection_stats5 cols

record_idINTEGER PRIMARY KEY
city_idINTEGER NOT NULL
dateTEXT NOT NULL
new_infectionsINTEGER NOT NULL
FOREIGNKEY(city_id) REFERENCES cities(city_id)

vaccine_distribution3 cols

city_idINTEGER PRIMARY KEY
vaccine_distribution_dateTEXT NOT NULL
FOREIGNKEY(city_id) REFERENCES cities(city_id)

survivor_camps6 cols

camp_idINTEGER PRIMARY KEY
city_idINTEGER NOT NULL
camp_nameTEXT NOT NULL
capacityINTEGER NOT NULL
current_populationINTEGER NOT NULL
FOREIGNKEY(city_id) REFERENCES cities(city_id)

Tables

4 tables

cities3 cols

city_idINTEGER PRIMARY KEY
city_nameTEXT NOT NULL
populationINTEGER NOT NULL

infection_stats5 cols

record_idINTEGER PRIMARY KEY
city_idINTEGER NOT NULL
dateTEXT NOT NULL
new_infections

All exercises in Zombie Virus – Part 3: The Cure

INTEGER NOT NULL

FOREIGNKEY(city_id) REFERENCES cities(city_id)

vaccine_distribution3 cols

city_idINTEGER PRIMARY KEY
vaccine_distribution_dateTEXT NOT NULL
FOREIGNKEY(city_id) REFERENCES cities(city_id)

survivor_camps6 cols

camp_idINTEGER PRIMARY KEY
city_idINTEGER NOT NULL
camp_nameTEXT NOT NULL
capacityINTEGER NOT NULL
current_populationINTEGER NOT NULL
FOREIGNKEY(city_id) REFERENCES cities(city_id)