Given a table of job postings, write a query to break down the number of users that have posted their jobs once versus the number of users that have posted at least one job multiple times.
job_postings
table
| column | type |
|---|---|
| id | integer |
| job_id | integer |
| user_id | integer |
| date_posted | datetime |
| column | type |
|---|---|
| posted_jobs_once | integer |
| posted_at_least_one_job_multiple_times | integer |
See my solution here.
We’re given two tables, a table of notification deliveries and a table of users, whose conversion_date column indicates when the user purchased LinkedIn Premium (if the user hasn’t purchased, the value is NULL.)
Write a query to get the distribution of total push notifications before a user converts to premium.
notification_deliveries
table
| column | type |
|---|---|
| notification | varchar |
| user_id | integer |
| created_at | datetime |
users
table
| column | type |
|---|---|
| user_id | integer |
| created_at | datetime |
| conversion_date | datetime |
| column | type |
|---|---|
| total_pushes | integer |
| frequency | integer |
See my solution here.
Given a table of users, write a query to get the cumulative number of new users added by day. Reset the count every month.
user_registrations
table
| columns | type |
|---|---|
| id | int |
| name | varchar |
| created_at | datetime |
| date | monthly cumulative |
|---|---|
| 2020-01-05 | 5 |
| 2020-01-02 | 12 |
| ... | ... |
| 2020-02-01 | 8 |
| 2020-02-02 | 17 |
| 2020-02-03 | 23 |
See my solution here.
Given a table of purchase transactions, find the top three most-bought items in 2020 for each category.
product_spend
table
| columns | type |
|---|---|
| transaction_id | int |
| cateory_id | int |
| product_id | int |
| user_id | int |
| spend | float |
| transaction_date | datetime |
| category_id | product_id | num_sold | rank |
|---|---|---|---|
| 1 | 10 | 13 | 1 |
| 1 | 30 | 9 | 2 |
| 1 | 70 | 5 | 3 |
| 2 | 90 | 21 | 1 |
| 2 | 60 | 20 | 1 |
| 2 | 40 | 13 | 3 |
See my solution here.