160. Sellers with no sales by day

hard ebay

  • *******************************************************************************************************************

Table 1: dates

Calendar dates from 01/01/2019 to 12/31/2025.

 col_name | col_type
 year     | smallint
 month    | smallint
 date     | date

Table 2: seller_status

Current seller status on the e-commerce platform.

  col_name       | col_type
seller_id        | bigint   
status           | varchar(20) -- 'suspended', 'active'

Table 3: transaction

E-Commerce transactions records

  col_name       | col_type
seller_id        | bigint   
transaction_id   | bigint
item_id          | bigint
total_amt        | float
transaction_dt   | date

Sample results

    date    | num_zero_sales
 2021-08-01 |              3
 2021-08-02 |              5
 2021-08-03 |              4
 2021-08-04 |              4
 2021-08-05 |              3
 2021-08-06 |              5
 2021-08-07 |              7

Solution postgres

WITH sellers AS (
    SELECT DISTINCT seller_id
    FROM seller_status
SELECT D.date, COUNT(CASE WHEN T.seller_id IS NULL THEN S.seller_id ELSE NULL END) AS num_zero_sales
FROM dates D
CROSS JOIN sellers S
LEFT JOIN transaction T
ON D.date = T.transaction_dt
AND S.seller_id = T.seller_id
WHERE D.date >= '2021-08-01'
AND D.date <= '2021-08-07'
ORDER BY D.date;


This query is trying to find out the number of sellers who didn't make any sales during the specified week (August 1-7, 2021).

To do this, the query first creates a temporary table called "sellers" which contains a list of all unique seller IDs from the "seller_status" table.

Then, the query uses the "dates" table to generate a list of dates within the specified week, and combines that with the "sellers" table using a cross join (which generates all possible combinations of dates and sellers).

Next, the query uses a left join to combine the "transaction" table with the previous result set. The left join ensures that all dates and sellers from the previous result set are included, even if there are no corresponding transactions.

The query then uses a case statement to count the number of sellers who didn't make any sales. If a seller didn't make any sales on a particular date, the case statement sets the value to 1 (which is then counted in the aggregate function).

Finally, the query groups the results by date and sorts them in ascending order.

Expected results

More Ebay questions

ID Title Level FTPR
159 Transaction volume from suspended accounts ebay easy
161 Biggest sales day ebay easy