Flipkart: Active F-Assured Listings per Seller — SQL Problem
The seller-success team reviews how much of each seller's catalogue carries the F-Assured badge (fast, quality-checked fulfilment).
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #650
Problem statement
The seller-success team reviews how much of each seller's catalogue carries the F-Assured badge (fast, quality-checked fulfilment).
For every seller, return seller_name and f_assured_listings, the number of their listings that are F-Assured (is_f_assured = 1) and have listing_status = active. A seller with none shows 0. Seller names are unique. Order by f_assured_listings descending, then seller_name ascending.
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: Seller
| Column | Type |
|---|---|
| seller_id | int |
| seller_name | varchar |
Primary key: seller_id.
One row per registered seller.
Table: Listing
| Column | Type |
|---|---|
| listing_id | int |
| seller_id | int |
| product_title | varchar |
| is_f_assured | bool |
| listing_status | enum(active, paused, blocked) |
Primary key: listing_id.
One row per product listing; seller_id always names a seller.
Examples
Example 1
Seller
| seller_id | seller_name |
|---|---|
| 1 | Kirana Kart |
| 2 | Pixel Point |
| 3 | Desi Weaves |
| 4 | BlueOak Home |
Listing
| listing_id | seller_id | product_title | is_f_assured | listing_status |
|---|---|---|---|---|
| 11 | 1 | Steel Bottle | 1 | active |
| 12 | 1 | LED Bulb Pack | 1 | paused |
| 13 | 2 | Power Bank | 1 | active |
| 14 | 2 | Wireless Earbuds | 1 | active |
| 15 | 3 | Cotton Kurta | 0 | active |
| 16 | 4 | Study Table | 1 | active |
| 17 | 4 | Non-stick Tawa | 1 | blocked |
Output
| seller_name | f_assured_listings |
|---|---|
| Pixel Point | 2 |
| BlueOak Home | 1 |
| Kirana Kart | 1 |
| Desi Weaves | 0 |
How to solve Flipkart: Active F-Assured Listings per Seller
The report must list sellers with zero badges too, so the query starts from Seller and LEFT JOINs the listings. The subtle part is where the two listing conditions go. Put them in WHERE and a seller whose only listings are paused would have every joined row filtered out — the seller vanishes instead of showing 0. Put them in the join's ON clause and the seller keeps one NULL-filled row.
COUNT(l.listing_id) then counts only real matches, because COUNT of a column skips NULLs; COUNT(*) would count the NULL row as 1. Grouping by seller_id as well as the name keeps the groups right even if names repeated.
The alternatives count with a correlated subquery per seller, or join everything and sum a CASE flag (with COALESCE for sellers with no listings at all). Each is one pass over the listings.
Reference solution (MySQL)
SELECT s.seller_name, COUNT(l.listing_id) AS f_assured_listings
FROM Seller s
LEFT JOIN Listing l
ON l.seller_id = s.seller_id AND l.is_f_assured = 1 AND l.listing_status = 'active'
GROUP BY s.seller_id, s.seller_name
ORDER BY f_assured_listings DESC, s.seller_nameAnother way
SELECT s.seller_name,
(SELECT COUNT(*) FROM Listing l WHERE l.seller_id = s.seller_id AND l.is_f_assured = 1 AND l.listing_status = 'active') AS f_assured_listings
FROM Seller s
ORDER BY f_assured_listings DESC, s.seller_nameAnother way
SELECT s.seller_name, COALESCE(SUM(CASE WHEN l.is_f_assured = 1 AND l.listing_status = 'active' THEN 1 ELSE 0 END), 0) AS f_assured_listings
FROM Seller s LEFT JOIN Listing l ON l.seller_id = s.seller_id
GROUP BY s.seller_id, s.seller_name
ORDER BY f_assured_listings DESC, s.seller_name← Flipkart: COD Orders Shipped to Pincodes Without COD · Flipkart: Orders Placed Each Day of Big Billion Days →