Ad Click-to-Purchase Rate

In digital advertising, "Conversion Rate" is a key performance indicator. Google and Amazon often look at attribution windows—specifically, did a user buy the product within a week of seeing the ad?

Write an SQL query to calculate the percentage of unique ad clicks that resulted in a purchase within a 7-day window.

Rules:

• A "Conversion" is defined as a purchase where purchase_date >= click_date AND purchase_date <= click_date + 7 days.
• Use unique click_id as the denominator.
• If a single click leads to multiple purchases in that window, it still only counts as ONE converted click.
• Output columns: total_clicks, converted_clicks, conversion_rate.
• Round conversion_rate to 2 decimal places.
• Return the result in descending order of conversion_rate.

Table Schema:

• AdClicks: click_id, user_id, ad_id, click_date (DATE).
• Purchases: purchase_id, user_id, product_id, purchase_date (DATE), amount.