How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

What difficulty level is this coding question?

This is a hard difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Google.

What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Google during technical interviews.

Compute Company Suggestion Funnels

Quick Overview

This question evaluates a candidate's ability to perform event-level funnel analysis including distinct aggregation, joins to a dimension table, time-windowed filtering, handling missing events (such as completions without prior clicks), and computing safe conversion rates using SQL or Python.

Company: Google

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: hard

Interview Round: Technical Screen

An AI workflow suggestion product logs suggestion-related events in a single event table. Table: `suggestion_events` - `event_id` STRING: unique event record ID. - `user_id` STRING: user who generated the event. - `company_id` STRING: company or tenant that the user belongs to. - `event_ts` TIMESTAMP: event timestamp stored in UTC. - `event_type` STRING: one of `shown`, `clicked`, or `completed`. - `suggestion_id` STRING: identifier of a suggestion instance shown to a user. Optional dimension table for the follow-up breakdown: Table: `suggestions` - `suggestion_id` STRING: primary key. - `suggestion_type` STRING: category of the suggestion, such as automation, debugging, cost optimization, or security. Relationship: `suggestion_events.suggestion_id` joins to `suggestions.suggestion_id`. Assume a suggestion can generate multiple event rows, so funnel counts should be based on distinct `suggestion_id` values within each company unless stated otherwise. Time window: use the last 30 days in UTC, defined as `event_ts >= analysis_ts - INTERVAL '30 days'` and `event_ts < analysis_ts`, where `analysis_ts` is the query run timestamp. Tasks: 1. For each `company_id`, calculate the suggestion funnel over the last 30 days. 2. Required output columns: `company_id`, `shown_count`, `clicked_count`, `completed_count`, `click_rate`, `completion_rate_from_shown`, `post_click_completion_rate`, and `completed_without_click_count`. 3. Define `shown_count` as distinct suggestions with at least one `shown` event, `clicked_count` as distinct suggestions with at least one `clicked` event, and `completed_count` as distinct suggestions with at least one `completed` event. 4. Define `click_rate = clicked_count / shown_count`, `completion_rate_from_shown = completed_count / shown_count`, and `post_click_completion_rate = completed_after_click_count / clicked_count`, using safe division when the denominator is zero. 5. If a suggestion has a `completed` event but no `clicked` event in the analysis window, include it in `completed_count`, exclude it from `completed_after_click_count`, and count it in `completed_without_click_count`. 6. Follow-up: modify the output to break down the same funnel by `suggestion_type` as well as `company_id`. If `suggestion_type` is not present on the event table, join to the `suggestions` dimension table.

Quick Answer: This question evaluates a candidate's ability to perform event-level funnel analysis including distinct aggregation, joins to a dimension table, time-windowed filtering, handling missing events (such as completions without prior clicks), and computing safe conversion rates using SQL or Python.

Quick Overview