112. Top 3 customers

easy amazon

  • *****************************************************************************.
  • **********************************************************

Table: orders

An eCommerce company's online order table.

  col_name    | col_type
--------------+-------------------
order_id      | bigint
product_id    | bigint
customer_id   | bigint
order_dt      | date
qty           | integer
unit_price_usd| float
channel       | varchar(20) -- mobile, desktop

Sample results

 customer_id |       spend
-------------+-------------------
        1008 | 8589.499999999995
        1030 | 8562.439999999993
        1041 | 8393.839999999998

Solution postgres

SELECT 
    customer_id, 
    SUM(qty * unit_price_usd) AS spend
FROM orders
GROUP BY customer_id
ORDER BY SPEND DESC
LIMIT 3;
    

Explanation

This query selects data from the "orders" table and groups it by the "customer_id" column. It then calculates the total amount spent by each customer by multiplying the quantity of each order by its unit price in US dollars and then summing those values. The result is sorted in descending order by the total amount spent and the top 3 customers are returned.

Expected results


More Amazon questions

ID Title Level FTPR
93 number of sales for each product this month amazon easy
20%
94 Top 3 products vs. bottom 3 products amazon medium
7%
95 Candidate products for subscription amazon hard
13%
108 Free premium membership amazon hard
16%
109 Top 3 most popular product categories amazon easy
11%
110 Most popular product by category amazon medium
12%
111 Second most popular product amazon medium
16%
113 Daily cumulative spend amazon easy
11%
114 Dod revenue change rate amazon easy
15%
115 Rolling average revenue amazon hard
28%
116 Top answers day by device amazon easy
14%
117 Rolling 7 days total answers amazon easy
35%
175 Employees' email account amazon easy
14%
176 Employees' annual bonus amazon easy
12%
177 Purchases by platform report amazon medium
10%