- ******************************************************************************************************.
Table 1: seller_status
Current seller status on the e-commerce platform.
col_name | col_type -----------------+--------------------- seller_id | bigint status | varchar(20) -- 'suspended', 'active'
Table 2: 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
sum ------- 79.97 (1 row)
Solution postgres
SELECT SUM(total_amt) AS volume
FROM seller_status S
INNER JOIN transaction T
ON T.seller_id = S.seller_id
WHERE S.status = 'suspended'
AND transaction_dt >= '2021-08-01'
AND transaction_dt <= '2021-08-31';
Explanation
This query is asking the database to add up the total amount of transactions made by sellers who are currently suspended in the month of August 2021.
To do this, the query is joining two tables, "seller_status" and "transaction", on the condition that the seller_id in both tables match. It then filters the results to only include transactions that occurred between August 1, 2021, and August 31, 2021, and only for sellers who have a status of "suspended" in the "seller_status" table.
Finally, the query renames the resulting sum of total_amt as "volume".
Expected results
More Ebay questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 160 | Sellers with no sales by day ebay | hard |
|
| 161 | Biggest sales day ebay | easy |
|