Flipkart: Product Questions Still Waiting for an Answer

Easy
652.

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. 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

ColumnType
question_idint
product_idint
asked_ondate

Primary key: question_id.

One row per question asked on a product page.

Table: QnaAnswer

ColumnTypeValues
answer_idint
question_idint
answered_byenumseller, buyer, brand
is_hiddenbool

Primary key: answer_id.

One row per answer; is_hidden = 1 when moderation took it down. question_id always names a question.

Example

The rows must come back in the order shown.

ProductQuestion

question_idproduct_idasked_on
15012024-08-02
25012024-08-05
35022024-08-01
45032024-08-05
55032024-08-07

QnaAnswer

answer_idquestion_idanswered_byis_hidden
101seller0
112buyer1
123buyer1
133brand0
145buyer1
155seller1

Output

question_idproduct_idasked_on
25012024-08-05
45032024-08-05
55032024-08-07
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
question_idint
product_idint
asked_ondate

Primary key: question_id.

One row per question asked on a product page.

Table: QnaAnswer

ColumnType
answer_idint
question_idint
answered_byenum(seller, buyer, brand)
is_hiddenbool

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_idproduct_idasked_on
15012024-08-02
25012024-08-05
35022024-08-01
45032024-08-05
55032024-08-07

QnaAnswer

answer_idquestion_idanswered_byis_hidden
101seller0
112buyer1
123buyer1
133brand0
145buyer1
155seller1

Output

question_idproduct_idasked_on
25012024-08-05
45032024-08-05
55032024-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_id

Another 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_id

Another 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 →