Using clicks and applications: for each product, compute CTR = unique users who applied after clicking / unique users…
SQLLevel: IntermediateCore interview patterns
Using clicks and applications: for each product, compute CTR = unique users who applied after clicking / unique users who clicked. Return product_name and ctr rounded to 2 decimals. Note: one user applied without clicking - they must not count.
Table definitions (from the source exercise):
CREATE TABLE clicks(click_id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, product_name TEXT, click_date TEXT);
CREATE TABLE applications(application_id INTEGER PRIMARY KEY, user_id INTEGER, product_id INTEGER, app_date TEXT, approved INTEGER);
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Approach: Start FROM clicks, LEFT JOIN applications on user AND product. COUNT(DISTINCT) both sides. The join direction handles the no-click applicant automatically.
SELECT c.product_name,
ROUND(1.0 * COUNT(DISTINCT ap.user_id) / COUNT(DISTINCT c.user_id), 2) AS ctr
FROM clicks c
LEFT JOIN applications ap ON ap.user_id = c.user_id
AND ap.product_id = c.product_id
GROUP BY c.product_id, c.product_name;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT