Sales Data Analysis API

Project Overview

This project is a backend API built using Node.js, Express, and SQLite. The goal is to provide an API for querying sales data and performing revenue calculations based on specific filters, such as product, category, and region.

The project loads sales data from a CSV file into an SQLite database, which can be queried for calculating various sales metrics. This API also supports a data refresh mechanism for reloading the data periodically or on-demand.

Technologies Used

• Node.js (v18.x)
• Express.js
• SQLite
• TypeScript
• CSV Parsing with fast-csv

---

Setup and Installation

Prerequisites

Make sure you have the following installed:

• Node.js (v18.x or higher)
• npm (v8.x or higher)
• TypeScript (v4.x or higher)

Installation Steps

1. Clone the Repository:

   git clone https://github.com/sh4t4d33p/sales-data-analysis-api.git
   cd sales-data-analysis-api

2. Install Dependencies:

   Run the following command to install the necessary dependencies:

   npm install

3. Compile TypeScript:

   To compile the TypeScript files into JavaScript, run:

   npx tsc

4. Start the Application:

   Once the TypeScript files are compiled, start the server by running:

   npm run start

5. Database Initialization:

   The data will be automatically loaded into an SQLite database (sales.db) from the CSV file on server startup.

---

API Endpoints

1. Get Total Revenue

• Route: /revenue/total

• Method: GET

• Query Parameters:

  • startDate (required): Start date (YYYY-MM-DD)
  • endDate (required): End date (YYYY-MM-DD)

Example:

GET /revenue/total?startDate=2023-01-01&endDate=2023-12-31

Response:

{
  "totalRevenue": 50000.00
}

---

2. Get Total Revenue by Product

• Route: /revenue/by-product

• Method: GET

• Query Parameters:

  • startDate (required): Start date (YYYY-MM-DD)
  • endDate (required): End date (YYYY-MM-DD)

Example:

GET /revenue/by-product?startDate=2023-01-01&endDate=2023-12-31

Response:

[
  {
    "Product_Name": "UltraBoost Running Shoes",
    "totalRevenue": 15000.00
  },
  {
    "Product_Name": "iPhone 15 Pro",
    "totalRevenue": 12000.00
  }
]

---

3. Get Total Revenue by Category

• Route: /revenue/by-category

• Method: GET

• Query Parameters:

  • startDate (required): Start date (YYYY-MM-DD)
  • endDate (required): End date (YYYY-MM-DD)

Example:

GET /revenue/by-category?startDate=2023-01-01&endDate=2023-12-31

Response:

[
  {
    "Category": "Shoes",
    "totalRevenue": 25000.00
  },
  {
    "Category": "Electronics",
    "totalRevenue": 15000.00
  }
]

---

4. Get Total Revenue by Region

• Route: /revenue/by-region

• Method: GET

• Query Parameters:

  • startDate (required): Start date (YYYY-MM-DD)
  • endDate (required): End date (YYYY-MM-DD)

Example:

GET /revenue/by-region?startDate=2023-01-01&endDate=2023-12-31

Response:

[
  {
    "Region": "North America",
    "totalRevenue": 25000.00
  },
  {
    "Region": "Europe",
    "totalRevenue": 15000.00
  }
]

---

Database Design

The database schema consists of a single table named orders, which stores sales data.

orders Table Schema

Column Name	Data Type	Description
Order_ID	TEXT	Unique identifier for the order
Product_ID	TEXT	Unique identifier for the product
Customer_ID	TEXT	Unique identifier for the customer
Product_Name	TEXT	Name of the product
Category	TEXT	Product category (e.g., Shoes, Electronics)
Region	TEXT	Region where the sale took place
Date_of_Sale	DATE	Date when the sale occurred
Quantity_Sold	INTEGER	Number of products sold
Unit_Price	REAL	Price per unit of the product
Discount	REAL	Discount percentage applied
Shipping_Cost	REAL	Shipping cost for the order
Payment_Method	TEXT	Payment method used (e.g., Credit Card)
Customer_Name	TEXT	Name of the customer
Customer_Email	TEXT	Email address of the customer
Customer_Address	TEXT	Address of the customer

---

Data Refresh Mechanism

To refresh the data:

• The loadData.ts script can be triggered manually to reload the data from the CSV file into the database.
• The refresh mechanism can be run on-demand via the API or periodically as required.

---

Project Structure

Here is an overview of the project structure:

sales-data-analysis-api/
├── dist/                    # Compiled JavaScript files
├── node_modules/            # Installed dependencies
├── src/                     # Source files
│   ├── database.ts          # Database connection handling
│   ├── loadData.ts          # ETL script for loading CSV data
│   ├── server.ts            # Express server setup
│   ├── services/            # Business logic services
│   │   └── revenueService.ts # Revenue calculation services
│   └── utils/               # Utility functions (if any)
├── .gitignore               # Git ignore file
├── package.json             # Project metadata and dependencies
├── tsconfig.json            # TypeScript configuration
└── README.md                # Project documentation

---