Load Data and Run Queries into SingleStore

Create a SingleStore Helios Workspace

If a workspace already exists, skip to the next step to create the database. Navigate to the Cloud Portal and follow these instructions to create a workspace.

Create a Database and a Table

Connect to your SingleStore deployment and run the following SQL commands to create a database named sales and associated table. Free shared tier users cannot create new databases.

For SingleStore Helios deployments, you can also run these commands in the SQL Editor, select Cloud Portal > Develop > Data Studio > SQL Editor.

Note

The SQL Editor only runs the queries that you select, so ensure you have them all selected before selecting Run.

SQL

CREATE DATABASE sales;
USE sales;

CREATE TABLE SalesData (
    Date TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    Store_ID BIGINT(20) DEFAULT NULL,
    ProductID TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    Product_Name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    Product_Category TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    Quantity_Sold BIGINT(20) DEFAULT NULL,
    Price FLOAT DEFAULT NULL,
    Total_Sales FLOAT DEFAULT NULL
);

Create and Run an S3 Pipeline

This step shows how to ingest the sample sales data from a public AWS S3 bucket into the SingleStore database using pipelines. Pipelines enable you to extract, transform, and load external data without needing third-party tools or middleware.

Note

For this example, you must set data_conversion_compatibility_level to a value of 7.5 or lower.

Run the following SQL command to create the pipeline:

SQL

CREATE PIPELINE SalesData_Pipeline AS
LOAD DATA S3 's3://singlestoreloaddata/SalesData/*.csv'
CONFIG '{ "region": "ap-south-1" }'
INTO TABLE SalesData
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 lines;

Run the following SQL command to start the pipeline:

SQL

START PIPELINE SalesData_Pipeline;

The pipeline ingests 18 million rows of data, which takes some time.

Verify the Pipeline Success

Query the pipelines_files information schema view to inspect the progress of the pipelines. The following command to check whether the SalesData_Pipeline pipeline has finished ingesting data.

SQL

SELECT * FROM information_schema.pipelines_files 
WHERE pipeline_name = "SalesData_Pipeline";

You can also run the pipelines in the foreground to easily verify that all the data has been ingested.

SQL

START PIPELINE <pipeline_name> FOREGROUND;

For SingleStore Helios deployments, you can also monitor the progress of your pipelines on the Cloud Portal, select Deployments > <your_workspace_group> > Databases > sales > Pipelines.

Run Queries on the Data

Once the data is loaded into your SingleStore database, the following queries can be used on the dataset.

Query 1: Total Sales for Each Product Category

This query finds the total sales for each product category.

SQL

SELECT Product_Category, SUM(Total_Sales) AS Total_Category_Sales
FROM SalesData
GROUP BY Product_Category
ORDER BY Total_Category_Sales DESC;

+------------------+----------------------+
| Product_Category | Total_Category_Sales |
+------------------+----------------------+
| Toys             |           2363715564 |
| Groceries        |           2363682480 |
| Pharmacy         |           2363608712 |
| Furniture        |           2362957132 |
| Clothing         |           2361497888 |
| Electronics      |           2360690400 |
| Pet Supplies     |           2358230168 |
+------------------+----------------------+

Query 2: Top Five Best-Selling Products by Quantity Sold

This query identifies the top five products with the highest quantity sold.

SQL

SELECT Product_Name, SUM(Quantity_Sold) AS Total_Quantity_Sold
FROM SalesData
GROUP BY Product_Name
ORDER BY Total_Quantity_Sold DESC
LIMIT 5;

+--------------+---------------------+
| Product_Name | Total_Quantity_Sold |
+--------------+---------------------+
| Coats        |             1355284 |
| Jeans        |             1352520 |
| Vests        |             1348256 |
| Sweaters     |             1347210 |
| Jackets      |             1346336 |
+--------------+---------------------+

Query 3: Monthly Sales Trend for a Specific Year

This query shows the monthly sales in the year 2024.

SQL

SELECT DATE_FORMAT(Date, '%Y-%m') AS Month, SUM(Total_Sales) AS Monthly_Sales
FROM SalesData
WHERE YEAR(Date) = 2024
GROUP BY Month
ORDER BY Month;

+---------+--------------------+
| Month   | Monthly_Sales      |
+---------+--------------------+
| 2024-01 | 1398745836.2047148 |
| 2024-02 | 1307658583.4984026 |
| 2024-03 | 1402409728.1016903 |
| 2024-04 | 1353704241.8081808 |
| 2024-05 | 1401236442.2752619 |
| 2024-06 | 1354872518.1564188 |
| 2024-07 |  90234796.49502373 |
+---------+--------------------+

Query 4: Top Three Stores with the Highest Sales in the Electronics Category

This query identifies the top three stores with the highest sales in the Electronics category.

SQL

SELECT Store_ID, SUM(Total_Sales) AS Electronics_Sales
FROM SalesData
WHERE Product_Category = 'Electronics'
GROUP BY Store_ID
ORDER BY Electronics_Sales DESC
LIMIT 3;

+----------+-------------------+
| Store_ID | Electronics_Sales |
+----------+-------------------+
|     1080 |      23858836.625 |
|     1014 |     23779521.1875 |
|     1062 |    23777309.65625 |
+----------+-------------------+

Query 5: Total Revenue by Product Category for a Specific Store

This query finds the total sales revenue for each product category for a specific store (Store ID 1084).

SQL

SELECT
   Store_ID,
   Product_Category,
   SUM(Total_Sales) AS Total_Revenue
FROM
   SalesData
WHERE
   Store_ID = 1084
GROUP BY
   Store_ID, Product_Category;

+----------+------------------+----------------+
| Store_ID | Product_Category | Total_Revenue  |
+----------+------------------+----------------+
|     1084 | Pharmacy         | 23310525.40625 |
|     1084 | Groceries        |   23128682.625 |
|     1084 | Electronics      | 23364940.28125 |
|     1084 | Clothing         | 23447722.09375 |
|     1084 | Furniture        | 23373544.15625 |
|     1084 | Toys             |  23508566.8125 |
|     1084 | Pet Supplies     | 23458592.53125 |
+----------+------------------+----------------+

Query 6: Top Three Most Profitable Days by Total Sales

This query identifies the top three days with the highest total sales across all stores.

SQL

SELECT Date, SUM(Total_Sales) AS Daily_Total_Sales
FROM SalesData
GROUP BY Date
ORDER BY Daily_Total_Sales DESC
LIMIT 3;

+------------+-------------------+
| Date       | Daily_Total_Sales |
+------------+-------------------+
| 2024-03-12 |      46100396.875 |
| 2023-10-04 |     46064106.5625 |
| 2023-08-10 |     45997283.8125 |
+------------+-------------------+

Query 7: Yearly Sales Growth Comparison

This query compares the total sales by year to analyze growth.

SQL

SELECT YEAR(Date) AS Year, SUM(Total_Sales) AS Annual_Sales
FROM SalesData
GROUP BY YEAR(Date)
ORDER BY Year;

+------+--------------+
| Year | Annual_Sales |
+------+--------------+
| 2023 |   8225510272 |
| 2024 |   8308821056 |
+------+--------------+

Query 8: Most Expensive Product Sold

This query finds the most expensive product sold, along with its details.

SQL

SELECT Product_Name, Price, Total_Sales
FROM SalesData
ORDER BY Price DESC
LIMIT 1;

+--------------+-------+-------------+
| Product_Name | Price | Total_Sales |
+--------------+-------+-------------+
| Corn         |   100 |         700 |
+--------------+-------+-------------+

Query 9: Average Quantity Sold per Product Category

This query calculates the average quantity sold for each product category.

SQL

SELECT Product_Category, AVG(Quantity_Sold) AS Average_Quantity_Sold
FROM SalesData
GROUP BY Product_Category;

+------------------+-----------------------+
| Product_Category | Average_Quantity_Sold |
+------------------+-----------------------+
| Pet Supplies     |               10.4948 |
| Toys             |               10.4935 |
| Electronics      |               10.5035 |
| Groceries        |               10.5019 |
| Pharmacy         |               10.4952 |
| Clothing         |               10.4956 |
| Furniture        |               10.4928 |
+------------------+-----------------------+

Load Data and Run Queries into SingleStore

On this page

Create a SingleStore Helios Workspace

Create a Database and a Table

Create and Run an S3 Pipeline

Verify the Pipeline Success

Run Queries on the Data

Query 1: Total Sales for Each Product Category

Query 2: Top Five Best-Selling Products by Quantity Sold

Query 3: Monthly Sales Trend for a Specific Year

Query 4: Top Three Stores with the Highest Sales in the Electronics Category

Query 5: Total Revenue by Product Category for a Specific Store

Query 6: Top Three Most Profitable Days by Total Sales

Query 7: Yearly Sales Growth Comparison

Query 8: Most Expensive Product Sold

Query 9: Average Quantity Sold per Product Category

Was this article helpful?

On this page

Was this article helpful?