Calculating Phishing Click-Through Rate

MediumSQL · PostgreSQL

Problem

Write a query to find the click-through rate of simulated phishing campaigns at KnowBe4, grouped by industry and organization size.

Use all simulated phishing campaigns and count only delivered messages in the denominator. A click-through rate is the percentage of delivered messages with a recorded click.

Output

One row per industry and organization size combination represented by delivered or attempted messages.
Columns: industry, organization_size, clicked_messages, delivered_messages, and click_through_rate_pct.
Return the rate as a percentage rounded to two decimal places, including zero or null when no messages were delivered.
Order by industry, then organization_size.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique simulated campaign identifier
organization_id	INT	Organization that launched the campaign
campaign_name	VARCHAR(150)	Campaign display name
campaign_type	VARCHAR(50)	Campaign category

organizations

Column	Type	Description
organization_idPK	INT	Unique organization identifier
organization_name	VARCHAR(150)	Organization name
industry	VARCHAR(100)	Organization industry
organization_size	VARCHAR(30)	Organization size segment

campaign_recipients

Column	Type	Description
recipient_event_idPK	INT	Unique recipient campaign record
campaign_id	INT	Campaign associated with the recipient
recipient_email	VARCHAR(200)	Recipient email address
delivered_at	TIMESTAMP	Timestamp when the simulated message was delivered
clicked_at	TIMESTAMP	Timestamp when the recipient clicked the simulated phishing link

Tablescampaignsorganizationscampaign_recipients