- *******************************************************************************************************************
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'
GROUP BY D.date
ORDER BY D.date;
Explanation
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 |
|