Speedy Check-In
You're an analyst working for a US airline called "You Knighted Airlines". The airline has an online check-in process, but it only works for people who have uploaded their passport, and if their uploaded passport hasn't expired before the time of their flight. In order to save more time at the check-in counter, the airline wants to promote this feature. The marketing team wants to send emails to users reminding them to upload up-to-date passports to enable speedy check-in.
The passengers are in the passengers table, and their passports are in passports.
| column_name | type |
|---|---|
id | INTEGER |
name | VARCHAR |
email | VARCHAR |
phone_no | VARCHAR |
mileage_number | VARCHAR |
registered_at | TIMESTAMPTZ |
| column_name | type | reference |
|---|---|---|
id | INTEGER | NULL |
user_id | INTEGER | passengers.id |
name | VARCHAR | NULL |
number | VARCHAR | NULL |
country | VARCHAR | NULL |
date_of_birth | DATE | NULL |
expires_at | DATE | NULL |
We will be sending emails to users if they haven't uploaded any passport. Furthermore, we also want to send an email if a user has a passport that will expire before any flights they've booked!
The passengers' flight info is in the flights table.
| column_name | type | reference |
|---|---|---|
id | INTEGER | NULL |
passenger_id | INTEGER | passengers.id |
origin | VARCHAR | NULL |
destination | VARCHAR | NULL |
takeoff_time | TIMESTAMPTZ | NULL |
conf_no | VARCHAR | NULL |
flight_no | VARCHAR | NULL |
Select the name and email for all passengers that meet the following criteria:
- Have at least 1 flight booked, and…
- Either haven't uploaded a passport, or their passport will expire before 1 of their booked flights departs.
Some users have multiple flights booked in the future. This may cause the same passenger to appear in the result more than once. We asked the marketing team if this is okay, and they say it is because they plan to send a user 1 email per flight.
DATEandTIMESTAMPTZcan be compared with the same operators as numbers.
