Global Entry
You're an analyst working for a US airline called "You Knighted Airlines". You are launching a new service to help people apply for the government's Global Entry program. The marketing team wants to send some promotional emails to all users after the launch. However, the process is a bit easier for US citizens, so they'll send a different email template to US citizens than non-citizens.
Your users are in the passengers table.
passengers
| column_name | type |
|---|---|
id | INTEGER |
name | VARCHAR |
email | VARCHAR |
phone_no | VARCHAR |
mileage_number | VARCHAR |
registered_at | TIMESTAMPTZ |
We'll use uploaded passports to see who is a US citizen, and determine which email template we want to send them. If a user hasn't uploaded a passport, we'll send them the email template for non-citizens.
passports
| 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 |
Select the following for all users:
nameemail- A boolean column aliased to
is_us_citizen,TRUEwhen they have uploaded a passport and its country is USA, otherwiseFALSE.
