CTR Query by Segment | Dataford Interview Questions

Write a SQL query to calculate the click-through rate (CTR) of ads segmented by industry and company size over the last 30 days for LinkedIn marketing campaigns.

Use the provided LinkedIn Ads, LinkedIn Companies, and LinkedIn Ad Events tables. Include only segments with at least one impression during the period. CTR is clicks divided by impressions, expressed as a percentage.

Output

One row per industry and company size segment.
Columns: industry, company_size, impressions, clicks, and ctr_percent.
Include only events from the last 30 days through today and segments with impressions greater than zero.
Order by industry ascending with null industries last, then company_size ascending.

Schema

linkedin_ads

Column	Type	Description
ad_idPK	INT	Unique LinkedIn ad identifier
campaign_id	INT	LinkedIn marketing campaign identifier
company_id	INT	Advertiser company identifier
ad_name	VARCHAR(100)	Human-readable ad name

linkedin_companies

Column	Type	Description
company_idPK	INT	Unique LinkedIn company identifier
industry	VARCHAR(100)	Company industry
company_size	VARCHAR(50)	Company size segment

linkedin_ad_events

Column	Type	Description
event_idPK	INT	Unique ad event record identifier
ad_id	INT	Referenced LinkedIn ad
event_date	DATE	Date on which the event occurred
event_type	VARCHAR(20)	Event type, such as impression or click
event_count	INT	Number of events represented by the row

Your question is CTR Query by Segment. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem