eCommerce Dataset

Overview

In this guide, you’ll learn how to use the Oxla database with eCommerce datasets through various sample queries. These datasets include information about sales transactions, such as customer details, supplier data, and order details.

You’ll explore how to analyze data by joining related tables and running queries to support informed decision-making for your business.

Data Source: https://github.com/apache/doris/tree/master/tools/ssb-tools .

Prerequisite
Ensure you’ve created the Data Storage with the appropriate demo dataset, the Cluster is running, and you’re connected to it using the PostgreSQL client. For more details, refer to the Quickstart - Oxla SaaS page.

Datasets Structure

Let’s explore the tables and their structures to understand better and fully utilize the eCommerce dataset.

customer Table

Attribute Name	Type
c_custkey	Integer
c_name	Text
c_address	Text
c_city	Text
c_nation	Text
c_region	Text
c_phone	Text
c_mktsegment	Text

lineorder Table

Attribute Name	Type
lo_orderkey	Integer
lo_linenumber	Integer
lo_custkey	Integer
lo_partkey	Integer
lo_suppkey	Integer
lo_orderdate	Date
lo_orderpriority	Text
lo_shippriority	Integer
lo_quantity	Integer
lo_extendedprice	Integer
lo_ordtotalprice	Integer
lo_discount	Integer
lo_revenue	Integer
lo_supplycost	Integer
lo_tax	Integer
lo_commitdate	Date
lo_shipmode	Text

supplier Table

Attribute Name	Type
s_suppkey	Integer
s_name	Text
s_address	Text
s_city	Text
s_nation	Text
s_region	Text
s_phone	Text

part Table

Attribute Name	Type
p_partkey	Integer
p_name	Text
p_mfgr	Text
p_category	Text
p_brand	Text
p_color	Text
p_type	Text
p_size	Integer
p_container	Text

Sample Queries

Sample 1: Find Customers with the Most Total Order

This example calculates the total order quantity for each customer by joining the customer and lineorder tables.

SELECT c.c_name AS customer_name, SUM(l.lo_quantity) AS total_order_quantity
FROM customer c
JOIN lineorder l ON c.c_custkey = l.lo_custkey
GROUP BY c.c_name
ORDER BY total_order_quantity DESC
LIMIT 10;

The result displays the 10 customer names with the highest total order quantity.

   customer_name    | total_order_quantity 
--------------------+----------------------
 Customer#002750845 |                15982
 Customer#002399458 |                15912
 Customer#001711105 |                15809
 Customer#001709917 |                15740
 Customer#000274669 |                15682
 Customer#001900141 |                15637
 Customer#001435495 |                15574
 Customer#002980366 |                15567
 Customer#000184897 |                15494
 Customer#002495023 |                15491
(10 rows)

Sample 2: Average Order Quantity and Revenue per Customer

In this example, we aim to retrieve the average order quantity and revenue per customer. We will use the LIMIT clause to find the top 5 customers based on their revenue.

SELECT c.c_name AS customer_name, 
       AVG(l.lo_quantity) AS avg_order_quantity, 
       AVG(l.lo_revenue) AS avg_order_revenue
FROM customer c
JOIN lineorder l ON c.c_custkey = l.lo_custkey
GROUP BY c.c_name
ORDER BY avg_order_revenue DESC
LIMIT 5;

This query displays the average order quantities and revenues for the top 5 customers.

   customer_name    | avg_order_quantity | avg_order_revenue 
--------------------+--------------------+-------------------
 Customer#002535380 | 31.348148148148148 |  4482488.65925926
 Customer#002547308 | 31.519083969465647 | 4419699.503816794
 Customer#002906426 | 29.959798994974875 | 4371241.924623116
 Customer#000640106 | 29.676470588235293 | 4370783.544117647
 Customer#002794070 | 29.681528662420384 | 4370103.968152867
(5 rows)

Sample 3: Find the Top 3 Sold Parts

This query lists the top 3 parts by total revenue and total quantity sold.

SELECT p.p_name AS part_name, 
       SUM(l.lo_quantity) AS total_quantity_sold, 
       SUM(l.lo_revenue) AS total_revenue
FROM lineorder l
JOIN part p ON l.lo_partkey = p.p_partkey
GROUP BY p.p_name
ORDER BY total_revenue DESC
LIMIT 3;

It displays the 3 parts that are most sold based on the quantity sold.

     part_name     | total_quantity_sold | total_revenue 
-------------------+---------------------+---------------
 aquamarine beige  |             3871448 |  562986438412
 aquamarine black  |             3662718 |  521680920074
 aquamarine bisque |             3692528 |  519083669350
(3 rows)

Sample 4: Monthly Revenue and Order Count per Supplier

This example shows how to calculate each supplier’s monthly revenue and order count.

SELECT s.s_name AS supplier_name, 
       EXTRACT(YEAR FROM l.lo_orderdate) AS order_year,
       EXTRACT(MONTH FROM l.lo_orderdate) AS order_month,
       COUNT(*) AS order_count,
       SUM(l.lo_revenue) AS total_revenue
FROM lineorder l
JOIN supplier s ON l.lo_suppkey = s.s_suppkey
JOIN part p ON l.lo_partkey = p.p_partkey
GROUP BY s.s_name, order_year, order_month
ORDER BY order_year, order_month, total_revenue DESC
LIMIT 10;

You will get the total revenue for each supplier during the respective period.

   supplier_name    | order_year | order_month | order_count | total_revenue 
--------------------+------------+-------------+-------------+---------------
 Supplier#000178170 |       1992 |           1 |          61 |     272171509
 Supplier#000024596 |       1992 |           1 |          64 |     261609404
 Supplier#000056984 |       1992 |           1 |          55 |     260734222
 Supplier#000163429 |       1992 |           1 |          56 |     259933505
 Supplier#000054770 |       1992 |           1 |          59 |     259102073
 Supplier#000141166 |       1992 |           1 |          63 |     259080854
 Supplier#000005060 |       1992 |           1 |          61 |     258319232
 Supplier#000065417 |       1992 |           1 |          59 |     256780075
 Supplier#000040250 |       1992 |           1 |          59 |     256695041
 Supplier#000171886 |       1992 |           1 |          56 |     255549592
(10 rows)