163. Average number of orders per brand

easy walmart

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

Table 1: walmart_brand

  col_name    | col_type
--------------+-------------------
brand_id      | bigint
brand_name    | text
product_id    | bigint

Table 2: walmart_order

  col_name    | col_type
--------------+-------------------
order_id      | bigint
product_id    | bigint
order_dt      | date
qty           | integer

Sample results

        avg
--------------------
 5.0000000000000000
(1 row)

Solution postgres

SELECT COUNT(order_id) * 1.0 / COUNT(DISTINCT B.brand_id) AS avg
FROM walmart_brand B
LEFT JOIN  walmart_order O
ON B.product_id = O.product_id
WHERE order_dt >= '2021-08-01'
AND   order_dt <= '2021-08-31'
;
    

Explanation

This query is trying to calculate the average number of orders per brand of products sold by Walmart in the month of August 2021.

The query starts by joining two tables, "walmart_brand" and "walmart_order", using a left join. The "walmart_brand" table contains information about the brands of products sold by Walmart, while the "walmart_order" table contains information about individual orders placed by customers.

The join is done based on the "product_id" field, which is present in both tables. This means that we are matching orders to the brands of the products that were ordered.

Next, the query filters the orders based on their order date, selecting only those orders that were placed in the month of August 2021.

Finally, the query calculates the average number of orders per brand by dividing the total number of orders (which is obtained by counting the "order_id" field in the "walmart_order" table) by the number of distinct brands (which is obtained by counting the "brand_id" field in the "walmart_brand" table). The "*1.0" is used here to make sure that the result is a decimal rather than an integer.

Overall, this query is useful for Walmart

Expected results


More Walmart questions

ID Title Level FTPR
162 Number of orders per brand walmart easy
41%
164 Orders distribution by brand walmart easy
28%
165 Session stitching walmart hard
17%
166 Top 10 customers based on spend growth walmart medium
11%