这是他们订购的第一个商人的客户？ SQL问题

发布于 2025-01-18 19:18:10 字数 973 浏览 4 评论 0原文

我正在努力解决这个sql问题：

对于每个商家，找出他们有多少订单和首次订单。首次订单是从客户的角度来看的，是客户下的第一个订单。换句话说，有多少顾客是他们第一次向该商家订购的？输出商户名称、订单总数以及首次下单的订单数。

订单
id int
customer_id int
Mercury_id int
order_timestamp datetime
n_items int
Total_amount_earned float

商家
id int
name varchar
Category varchar
order_timestamp datetime
zipcode int

这是我到目前为止所拥有的：

with first_time_orders as (
  select customer_id, rank() over (partition by merchant_id order by order_timestamp) as first_order
  from orders
  group by customer_id, merchant_id
)

select *
from first_time_orders;

原文

I am struggling with this sql question:

For each merchant, find how many orders and first-time orders they had. First-time orders are meant from the perspective of a customer and are the first order that a customer ever made. In order words, for how many customers was this the first-ever merchant they ordered with?
Output the name of the merchant, the total number of their orders and the number of these orders that were first-time orders.

orders
id int
customer_id int
merchant_id int
order_timestamp datetime
n_items int
total_amount_earned float

merchants
id int
name varchar
category varchar
order_timestamp datetime
zipcode int

this is what i have so far:

with first_time_orders as (
  select customer_id, rank() over (partition by merchant_id order by order_timestamp) as first_order
  from orders
  group by customer_id, merchant_id
)

select *
from first_time_orders;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乄_柒ぐ汐 2025-01-25 19:18:10

您可以

用cter 尝试一下
（选择，
dense_rank（）over（customer_id订单分区订单dimestamp）h
来自doordash_orders）
选择 *来自（选择Merchant_ID，count（）GB的cter Group by Merchant_id）h
加入
（从cter中选择“商品_id”，计数（*）gbb，其中h = 1组由erthant_id）p
在h.merchant_id上= p.merchant_id