Flipkart: Product Questions Still Waiting for an Answer
Shoppers ask questions on product pages, and sellers, past buyers or the brand answer them. Moderation hides answers that break the guidelines.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #652
Problem statement
Shoppers ask questions on product pages, and sellers, past buyers or the brand answer them. Moderation hides answers that break the guidelines. The catalogue team wants the questions shoppers still see with no answer.
Return every question that has no visible answer — no answer at all, or only answers with is_hidden = 1. Return question_id, product_id and asked_on, ordered by asked_on, then question_id.
A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.
Tables
Table: ProductQuestion
| Column | Type |
|---|---|
| question_id | int |
| product_id | int |
| asked_on | date |
Primary key: question_id.
One row per question asked on a product page.
Table: QnaAnswer
| Column | Type |
|---|---|
| answer_id | int |
| question_id | int |
| answered_by | enum(seller, buyer, brand) |
| is_hidden | bool |
Primary key: answer_id.
One row per answer; is_hidden = 1 when moderation took it down. question_id always names a question.
Examples
Example 1
ProductQuestion
| question_id | product_id | asked_on |
|---|---|---|
| 1 | 501 | 2024-08-02 |
| 2 | 501 | 2024-08-05 |
| 3 | 502 | 2024-08-01 |
| 4 | 503 | 2024-08-05 |
| 5 | 503 | 2024-08-07 |
QnaAnswer
| answer_id | question_id | answered_by | is_hidden |
|---|---|---|---|
| 10 | 1 | seller | 0 |
| 11 | 2 | buyer | 1 |
| 12 | 3 | buyer | 1 |
| 13 | 3 | brand | 0 |
| 14 | 5 | buyer | 1 |
| 15 | 5 | seller | 1 |
Output
| question_id | product_id | asked_on |
|---|---|---|
| 2 | 501 | 2024-08-05 |
| 4 | 503 | 2024-08-05 |
| 5 | 503 | 2024-08-07 |
How to solve Flipkart: Product Questions Still Waiting for an Answer
This is an anti join: keep a question when there is no row in QnaAnswer that is both for this question and visible. NOT EXISTS says exactly that — the correlated subquery looks for one visible answer, and the question survives only if it finds none.
The visibility test belongs inside the subquery. A question whose only answers were hidden still has answer rows, so checking for "no answers" and then filtering hidden ones afterwards would wrongly drop it. The same rule applies to the LEFT JOIN version: a.is_hidden = 0 must sit in the ON clause, so a question with only hidden answers gets a NULL-filled row and passes a.answer_id IS NULL.
The NOT IN form is safe because question_id in the answers is never NULL. All three read each question once with an index lookup into the answers. Ties on asked_on are broken by question_id.
Reference solution (MySQL)
SELECT q.question_id, q.product_id, q.asked_on
FROM ProductQuestion q
WHERE NOT EXISTS (
SELECT 1 FROM QnaAnswer a
WHERE a.question_id = q.question_id AND a.is_hidden = 0
)
ORDER BY q.asked_on, q.question_idAnother way
SELECT q.question_id, q.product_id, q.asked_on
FROM ProductQuestion q
LEFT JOIN QnaAnswer a ON a.question_id = q.question_id AND a.is_hidden = 0
WHERE a.answer_id IS NULL
ORDER BY q.asked_on, q.question_idAnother way
SELECT question_id, product_id, asked_on FROM ProductQuestion
WHERE question_id NOT IN (SELECT question_id FROM QnaAnswer WHERE is_hidden = 0)
ORDER BY asked_on, question_id← Flipkart: Orders Placed Each Day of Big Billion Days · Flipkart: Pay Later Accounts Spending Past Their Limit →