sqlite meta招聘SQL --加入第2部分

lawou6xi 于 2023-01-31 发布在 SQLite

关注(0)|答案(2)|浏览(132)

以下方案是连锁杂货店的关系数据库的子集。该连锁店通过不同的商店向客户销售不同产品类别的许多产品。它还进行许多不同的促销活动。
我们要分析的四个表之间的关系如下所示：

+------------------+---------+         +---------------------+---------+
        | product_id       | INTEGER |>--------| product_id          | INTEGER |
        | store_id         | INTEGER |    +---<| product_class_id    | INTEGER |
        | customer_id      | INTEGER |    |    | brand_name          | VARCHAR |
   +---<| promotion_id     | INTEGER |    |    | product_name        | VARCHAR |
   |    | store_sales      | DECIMAL |    |    | is_low_fat_flg      | TINYINT |
   |    | store_cost       | DECIMAL |    |    | is_recyclable_flg   | TINYINT |
   |    | units_sold       | DECIMAL |    |    | gross_weight        | DECIMAL |
   |    | transaction_date | DATE    |    |    | net_weight          | DECIMAL |
   |    +------------------+---------+    |    +---------------------+---------+
   |                                      |
   |    # promotions                      |    # product_classes
   |    +------------------+---------+    |    +---------------------+---------+
   +----| promotion_id     | INTEGER |    +----| product_class_id    | INTEGER |
        | promotion_name   | VARCHAR |         | product_subcategory | VARCHAR |
        | media_type       | VARCHAR |         | product_category    | VARCHAR |
        | cost             | DECIMAL |         | product_department  | VARCHAR |
        | start_date       | DATE    |         | product_family      | VARCHAR |
        | end_date         | DATE    |         +---------------------+---------+
        +------------------+---------+

 /*
 PROMPT:
 -- The VP of Sales feels that some product categories don't sell
 -- and can be completely removed from the inventory.
 -- As a first pass analysis, they want you to find what percentage
 -- of product categories have never been sold.
 
 EXPECTED OUTPUT:
 Note: Please use the column name(s) specified in the expected output in your solution.
 +-----------------------------------+
 | pct_product_categories_never_sold |
 +-----------------------------------+
 |               13.8888888888888889 |
 +-----------------------------------+

他们想找出从未售出的产品类别的百分比
我尝试了这个解决方案：

SELECT Count(*) * 100.0 / (SELECT Count(*)
                           FROM   product_classes) AS
       pct_product_categories_never_sold
FROM   product_classes AS C
       INNER JOIN products AS P
               ON C.product_class_id = P.product_class_id
       LEFT JOIN sales AS S
              ON S.product_id = P.product_id
WHERE  S.product_id IS NULL

这就是结果：

pct_product_categories_never_sold 
 ---------------------------------
      37.9746835443038

sqlite

来源：https://stackoverflow.com/questions/72412395/meta-recruiting-sql-join-part-2

2条答案

按热度按时间

mklgxw1f1#

您可以使用NOT EXISTS检查每个产品类别是否已经售出。
NOT EXISTS的结果是一个布尔值，计算结果为1或0，您可以使用AVG()对其进行聚合：

SELECT 100 * AVG (
  NOT EXISTS (
    SELECT 1
    FROM products p INNER JOIN sales s
    ON s.product_id = p.product_id
    WHERE p.product_class_id = c.product_class_id
  )
) pct_product_categories_never_sold
FROM product_classes c;

参见简化的demo。

赞(0）回复(0）举报 2023-01-31

ndasle7k2#

正确的解决方案如下：

SELECT 100.0 * (1 - COUNT(DISTINCT product_category) / CAST((SELECT COUNT(DISTINCT product_category) FROM product_classes) AS FLOAT64)) AS pct_product_categories_never_sold
FROM sales s JOIN products p ON (s.product_id=p.product_id) JOIN product_classes pc ON (p.product_class_id=pc.product_class_id)

赞(0）回复(0）举报 2023-01-31

我来回答

sqlite meta招聘SQL --加入第2部分

2条答案

相关问题

热门标签

最新问答