Average Order Value by Settlement

SQL query for N11

Calculate the average order value for each settlement.

SELECT s.settlement_name, AVG(o.total_price) AS average_order_value FROM settlements s INNER JOIN orders o ON s.settlement_id = o.settlement_id GROUP BY s.settlement_name;

N11 data model

Table Orders

Id: Integer
Status: Integer
CreateDate: Date-time
OrderNumber: Integer
PaymentType: Integer
TotalAmount: Integer
CitizenshipId: String
TotalDiscountAmount: Integer
OrderItemList_orderItem_id: Integer
OrderItemList_orderItem_price: Integer
OrderItemList_orderItem_bundle: String
OrderItemList_orderItem_status: Integer
OrderItemList_orderItem_version: Integer
OrderItemList_orderItem_quantity: Integer
OrderItemList_orderItem_dueAmount: Number
OrderItemList_orderItem_productId: Integer
OrderItemList_orderItem_attributes: String
OrderItemList_orderItem_commission: Number
OrderItemList_orderItem_productName: String
OrderItemList_orderItem_sppApproved: String
OrderItemList_orderItem_updatedDate: Date-time
OrderItemList_orderItem_approvedDate: Date-time
OrderItemList_orderItem_mallDiscount: Integer
OrderItemList_orderItem_shippingDate: Date-time
OrderItemList_orderItem_sellerDiscount: Integer
OrderItemList_orderItem_deliveryFeeType: Integer
OrderItemList_orderItem_productSellerCode: String
OrderItemList_orderItem_stockKeepingUnitId: Integer
OrderItemList_orderItem_sellerInvoiceAmount: Integer
OrderItemList_orderItem_sellerCouponDiscount: Integer
OrderItemList_orderItem_customTextOptionValues: String
OrderItemList_orderItem_totalMallDiscountPrice: Number
OrderItemList_orderItem_installmentChargeWithVAT: Integer
OrderItemList_orderItem_shipmentInfo_shipmentCode: Integer
OrderItemList_orderItem_shipmentInfo_campaignNumber: Integer
OrderItemList_orderItem_shipmentInfo_shipmentMethod: Integer
OrderItemList_orderItem_shipmentInfo_trackingNumber: String
OrderItemList_orderItem_shipmenCompanyCampaignNumber: Integer
OrderItemList_orderItem_shipmentInfo_shipmentCompany_id: Integer
OrderItemList_orderItem_shipmentInfo_campaignNumberStatus: Integer
OrderItemList_orderItem_shipmentInfo_shipmentCompany_name: String
OrderItemList_orderItem_shipmentInfo_shipmentCompany_shortName: String

Table Orderlines

Id: Integer
Price: Integer
Bundle: String
Status: Integer
Version: Integer
Order_id: Integer
Quantity: Integer
DueAmount: Number
ProductId: Integer
Attributes: String
Commission: Number
ProductName: String
SppApproved: String
UpdatedDate: Date-time
ApprovedDate: Date-time
MallDiscount: Integer
ShippingDate: Date-time
SellerDiscount: Integer
DeliveryFeeType: Integer
ProductSellerCode: String
StockKeepingUnitId: Integer
SellerInvoiceAmount: Integer
SellerCouponDiscount: Integer
CustomTextOptionValues: String
TotalMallDiscountPrice: Number
InstallmentChargeWithVAT: Integer
ShipmentInfo_shipmentCode: Integer
ShipmentInfo_campaignNumber: Integer
ShipmentInfo_shipmentMethod: Integer
ShipmentInfo_trackingNumber: String
ShipmenCompanyCampaignNumber: Integer
ShipmentInfo_shipmentCompany_id: Integer
ShipmentInfo_campaignNumberStatus: Integer
ShipmentInfo_shipmentCompany_name: String
ShipmentInfo_shipmentCompany_shortName: String

Table Top Level Categories

Table Products

Table Settlements

Magical SQL for N11 with Peliqan.io

Explore data in a rich spreadsheet UI. Use Magical SQL to combine and transform data. SQL queries become new tables that can be shared with business users and used in any BI tool such as Microsoft Power BI, Tableau or Metabase.

Let AI do the work for you

Peliqan’s AI assistant helps you to write SQL queries to get to insights fast. Ask your question in plain English and immediately see the result in Peliqan’s rich spreadsheet viewer.