SQL Top Pages by Conversion Drop | Dataford Interview Questions

Write a SQL query to identify the top three pages with the largest week-over-week drop in conversion rate.

Use the page_weekly_metrics table. Treat conversion rate as conversions divided by visits, compare consecutive weekly records, and return each page's largest observed decline.

Output

One row per page, with at most three rows.
Columns: page_name, week_start, previous_conversion_rate, conversion_rate, and drop_percentage_points.
Include only positive declines, ordered by drop_percentage_points descending, then page_name ascending.

Schema

page_weekly_metrics

Column	Type	Description
metric_idPK	INT	Unique weekly metric record identifier
page_name	VARCHAR(255)	Page being measured
week_start	DATE	Start date of the reporting week
visits	INT	Number of page visits during the week
conversions	INT	Number of conversions attributed to the page

Your question is SQL Top Pages by Conversion Drop. Start with the requirements and the one table 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