2329. Product Sales Analysis V

最新推荐文章于 2024-11-06 11:12:27 发布

青色信笺

最新推荐文章于 2024-11-06 11:12:27 发布

阅读量170

点赞数

分类专栏：力扣sql题文章标签：大数据

本文链接：https://blog.csdn.net/m0_69157845/article/details/125645376

版权

力扣sql题专栏收录该内容

183 篇文章 4 订阅

订阅专栏

根据给定的SQL架构，需要编写一个查询来报告每个用户的消费总额。表格包括Sales和Product，分别记录销售详情和产品价格。查询首先通过左连接将两个表合并，然后按用户ID分组并计算每个用户的消费总和，最后按消费金额降序排列，若有相同消费，则按用户ID升序排列。

摘要由CSDN通过智能技术生成

SQL架构

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| user_id     | int   |
| quantity    | int   |
+-------------+-------+
sale_id is the primary key of this table.
product_id is a foreign key to Product table.
Each row of this table shows the ID of the product and the quantity purchased by a user.

Table: Product

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id is the primary key of this table.
Each row of this table indicates the price of each product.

Write an SQL query that reports the spending of each user.

Return the resulting table ordered by spending in descending order. In case of a tie, order them by user_id.

The query result format is in the following example.

Example 1:

Input: 
Sales table:
+---------+------------+---------+----------+
| sale_id | product_id | user_id | quantity |
+---------+------------+---------+----------+
| 1       | 1          | 101     | 10       |
| 2       | 2          | 101     | 1        |
| 3       | 3          | 102     | 3        |
| 4       | 3          | 102     | 2        |
| 5       | 2          | 103     | 3        |
+---------+------------+---------+----------+
Product table:
+------------+-------+
| product_id | price |
+------------+-------+
| 1          | 10    |
| 2          | 25    |
| 3          | 15    |
+------------+-------+
Output: 
+---------+----------+
| user_id | spending |
+---------+----------+
| 101     | 125      |
| 102     | 75       |
| 103     | 75       |
+---------+----------+
Explanation: 
User 101 spent 10 * 10 + 1 * 25 = 125.
User 102 spent 3 * 15 + 2 * 15 = 75.
User 103 spent 3 * 25 = 75.
Users 102 and 103 spent the same amount and we break the tie by their ID while user 101 is on the top.

# Write your MySQL query statement below
select
s.user_id,sum(s.quantity * p.price) spending
from
Sales s left join Product  p 
using(product_id) 
group by s.user_id
order by spending desc, user_id