Znote (recipes)
  Get Znote  

Sales report (SQL preparation)

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

 

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

Function used to connect your database and declared in function editor (bottom right)

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');

Related recipes