Skip to content
Quantoros

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

Related questions