← 返回 meta 的题目列表SQL Query for Connected Ad Tables
类型：online_judge
Given three SQL tables: ad, impression, and conversion, design a query to calculate the Click-Through Rate (CTR) for each ad, i.e., the ratio of clicks to impressions.
Specifications:
The ad table contains basic information about the ads.
The impression table records the impressions of the ads.
The conversion table records the clicks of the ads.
Write an SQL query to calculate the CTR for each ad.
Display the table structure and example data:
-- Ad Table
CREATE TABLE ad (
ad_id INT PRIMARY KEY,
ad_name VARCHAR(255)
);
-- Impression Table
CREATE TABLE impression (
impression_id INT PRIMARY KEY,
ad_id INT,
impression_count INT
);
-- Conversion Table
CREATE TABLE conversion (
conversion_id INT PRIMARY KEY,
ad_id INT,
conversion_count INT
);
Example Input:
-- Example Rows
INSERT INTO ad (ad_id, ad_name) VALUES (1, 'AdA'), (2, 'AdB');
INSERT INTO impression (impression_id, ad_id, impression_count) VALUES (1, 1, 100), (2, 2, 200);
INSERT INTO conversion (conversion_id, ad_id, conversion_count) VALUES (1, 1, 10), (2, 2, 20);
Example Output: Each ad's CTR like:
| ad_name | CTR |
|---------|------|
| AdA | 0.10 |
| AdB | 0.10 |
Example
Input
ad: [(1, 'AdA'), (2, 'AdB')], impression: [(1, 1, 100), (2, 2, 200)], conversion: [(1, 1, 10), (2, 2, 20)]