获得前50名的大查询

Question

我们只有StoreId。我想根据他们在过去30天中订购的订单数量(一个customerIds可以有多个orderId)，编写一个获得前50位orderId的查询。

数据模式：

Table1 : 
     StoreId : String, NOTNULL  
     CustomerId : String, NOTNULL
     Type : String, NOTNULL (Either visit or order, we are looking for order)
     OrderId : String (NOTNULL if Type is order)
     Timestamp : Timestamp

Table2 : 
     
     StoreId : String, NOTNULL  
     OrderId : String, NOTNULL
     AmountOfOrder: Float, NOTNULL

注意-我还在学习阶段。

Answer 1

尝试下面的查询逻辑，如果您不需要TotalOrders，那么从select中删除它。

select customerId, sum(amountOfOrder) as TotalOrders 
     from Table1 t1 join Table2 t2 on t1.StoreId = t2.StoreId and t1.OrderId = t2.OrderId
     Group by customerId
     Order by sum(amountOfOrder) desc
     limit 50;