Sales report (SQL preparation)

This note explains how to create your database for the Sales Report sample template.

Packages

NPM packages used for a MySQL database

npm i -S sequelize
npm i -S mysql2
npm i -S tablify

See this blog post to see alternative databases

Functions

Copy/Paste and save the code below into Custom functions section to connect your database 👉 (bottom right button f(x))

async function localSalesSQL(sqlQuery) {
  const { Sequelize, QueryTypes } = require('sequelize');
  const sequelize = new Sequelize("sales", "root", "root", {  
    dialect: "mysql"/* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
    host: "localhost"
  });
  sequelize.authenticate();
  const [results, metadata] = await sequelize.query(sqlQuery);
  sequelize.close()
  return results;
}

Database schema

-- Mysql schema
CREATE TABLE Customers (
    customer_id int NOT NULL AUTO_INCREMENT,
    customer_name varchar(255),
    region varchar(255),
    PRIMARY KEY (customer_id)
);
CREATE TABLE Products (
    product_id int NOT NULL AUTO_INCREMENT,
    product_name varchar(255),
    category varchar(255),
    PRIMARY KEY (product_id)
);
CREATE TABLE Sales (
    sales_id int NOT NULL AUTO_INCREMENT,
    customer_id int,
    product_id int,
    quantity int,
    price DECIMAL(10,2),
    sale_date DATE,
    channel varchar(255),
    PRIMARY KEY (sales_id)
);

Populate data

INSERT INTO Customers (customer_name, region)
VALUES
('Charlie Chaplin', 'North'),
('Beyonce Beatle', 'South'),
('Mick Mercury', 'East'),
('Diana Drake', 'West'),
('Freddie Fitzgerald', 'North'),
('Amy Adams', 'South'),
('Jennifer Joplin', 'East'),
('Elvis Evans', 'West');

INSERT INTO Products (product_name, category)
VALUES
('Falcon Flux', 'Hypercar'),
('Nebula Navigator', 'Spacecraft'),
('Quantum Quattro', 'Electric Car'),
('Galaxy Glider', 'Airbike'),
('Orion Overdrive', 'Hypercar'),
('Zephyr Zest', 'Spacecraft'),
('Comet Cruiser', 'Electric Car'),
('Astro Antelope', 'Airbike');

INSERT INTO Sales (customer_id, product_id, quantity, price, sale_date, channel)
VALUES
(1, 1, 5, 150000.00, '2023-01-15', 'Facebook'),
(1, 1, 5, 50000.00, '2023-01-15', 'Google'),
(1, 1, 5, 25000.00, '2023-01-15', 'Linkedin'),
(1, 2, 10, 200000.00, '2023-01-20', 'Facebook'),
(2, 1, 3, 150000.00, '2023-02-05', 'Google'),
(3, 3, 8, 60000.00, '2023-02-10', 'Linkedin'),
(4, 4, 2, 50000.00, '2023-02-15', 'Google'),
(2, 2, 5, 200000.00, '2023-02-20', 'Facebook'),
(3, 1, 7, 150000.00, '2023-03-05', 'Referral'),
(4, 3, 4, 60000.00, '2023-03-10', 'Google'),
(1, 4, 6, 50000.00, '2023-03-15', 'Referral'),
(3, 2, 9, 200000.00, '2023-03-20', 'Facebook'),
(5, 5, 7, 180000.00, '2023-04-05', 'Facebook'),
(6, 6, 4, 220000.00, '2023-04-10', 'Facebook'),
(7, 7, 5, 65000.00, '2023-04-15', 'Linkedin'),
(8, 8, 6, 52000.00, '2023-04-20', 'Referral');