Back to questions
Webinar Popularity Snowflake SQL Interview Question
Webinar Popularity
Snowflake SQL Interview Question
As a Data Analyst on Snowflake's Marketing Analytics team, you're analyzing the CRM to determine what percent of marketing touches were of type "webinar" in April 2022. Round your percentage to the nearest integer.
Did you know? Marketing touches, also known as touch points are the brand's (Snowflake's) point of contact with the customers, from start to finish.
Table:
| Column Name | Type |
|---|---|
| event_id | integer |
| contact_id | integer |
| event_type | string |
| event_date | date |
Example Input:
| event_id | contact_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | webinar | 4/17/2022 |
| 2 | 1 | trial_request | 4/23/2022 |
| 3 | 1 | whitepaper_download | 4/30/2022 |
| 4 | 2 | handson_lab | 4/19/2022 |
| 5 | 2 | trial_request | 4/23/2022 |
| 6 | 2 | conference_registration | 4/24/2022 |
| 7 | 3 | whitepaper_download | 4/30/2022 |
| 8 | 4 | trial_request | 4/30/2022 |
| 9 | 4 | webinar | 5/14/2022 |
Example Output:
| webinar_pct |
|---|
| 13 |
Explanation:
In April 2022, there is 1 webinar out of a total of 8 marketing events. Thus, the ratio is 1/8 = 12.5% ~ 13%.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Filter for the events only in April 2022
To filter for the month of April 2022, we have 2 methods:
- Filter using a combination of comparison operators - i.e.
- Filter using . This function truncates the date to a given granularity. In our case, any given date in April will be truncated to '04/01/2022'. Thus, we could filter .
Feel free to use either option. If you need more reading about the function, click here.
Step 2: Find the number of webinar events, and the total number of events
Next, we want to obtain the number of webinar events. To do this, we can utilize the statement. Thus, if the is 'webinar', we assign the value 1, otherwise 0. Keep in mind that this allocates the value 1 to rows only, so to get the total number, we can simply use .
To obtain the number of total events, we can just count all of the rows in the dataset.
| webinar_events | total_events |
|---|---|
| 2 | 10 |
Step 3: Calculate the percentage of webinar events
We are almost done - one simple step left. To obtain the percentage, first, divide the webinar events by the total amount of events, and multiply by 100.
Lastly, round the result to 0 decimals (the default value for the function is 0, so you don't need to pass that argument at all).