Flipkart: Active F-Assured Listings per Seller

Easy
650.

Flipkart: Active F-Assured Listings per Seller

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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per registered seller.

Table: Listing

ColumnTypeValues
listing_idint
seller_idint
product_titlevarchar
is_f_assuredbool
listing_statusenumactive, paused, blocked

Primary key: listing_id.

One row per product listing; seller_id always names a seller.

Example

The rows must come back in the order shown.

Seller

seller_idseller_name
1Kirana Kart
2Pixel Point
3Desi Weaves
4BlueOak Home

Listing

listing_idseller_idproduct_titleis_f_assuredlisting_status
111Steel Bottle1active
121LED Bulb Pack1paused
132Power Bank1active
142Wireless Earbuds1active
153Cotton Kurta0active
164Study Table1active
174Non-stick Tawa1blocked

Output

seller_namef_assured_listings
Pixel Point2
BlueOak Home1
Kirana Kart1
Desi Weaves0
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: 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

ColumnType
seller_idint
seller_namevarchar

Primary key: seller_id.

One row per registered seller.

Table: Listing

ColumnType
listing_idint
seller_idint
product_titlevarchar
is_f_assuredbool
listing_statusenum(active, paused, blocked)

Primary key: listing_id.

One row per product listing; seller_id always names a seller.

Examples

Example 1

Seller

seller_idseller_name
1Kirana Kart
2Pixel Point
3Desi Weaves
4BlueOak Home

Listing

listing_idseller_idproduct_titleis_f_assuredlisting_status
111Steel Bottle1active
121LED Bulb Pack1paused
132Power Bank1active
142Wireless Earbuds1active
153Cotton Kurta0active
164Study Table1active
174Non-stick Tawa1blocked

Output

seller_namef_assured_listings
Pixel Point2
BlueOak Home1
Kirana Kart1
Desi Weaves0

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_name

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

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