📊 Sales Trend Analysis Using SQL – Task 6

A Data Analyst Internship Project

📌 Project Overview

This repository contains the solution for Task 6: Sales Trend Analysis Using Aggregations, as described in the internship task file (page 1)

task 6-1

Objective:

Analyze monthly revenue and order volume using SQL aggregations.

Deliverables:

SQL Script

Results Table (CSV or screenshot)

📁 Dataset Used

The dataset provided contains daily coffee shop sales:

File: index_1.csv Columns include:

date (order date)

money (amount)

card (order ID)

coffee_name (product name)

cash_type

datetime

For SQL analysis, the dataset was mapped to:

SQL Column Dataset Column order_id card order_date date amount money product_id coffee_name 🛠 Tools Used

SQLite (DB Browser for SQLite)

SQL Query Editor

Provided CSV dataset

SQLite was chosen because it is simple and beginner-friendly, as allowed in the task instructions.

📊 SQL Tasks Performed

Following the mini guide from the task description (page 1)

task 6-1

, the following SQL operations were performed:

✔ 1. Extract month & year

Using strftime('%Y', order_date) and strftime('%m', order_date).

✔ 2. Group data by month

Using GROUP BY year, month.

✔ 3. Calculate revenue

Using SUM(amount).

✔ 4. Count order volume

Using COUNT(DISTINCT order_id).

✔ 5. Sort results

Using ORDER BY.

✔ 6. Retrieve top-performing months

Using LIMIT.

📜 SQL Script 1️⃣ Monthly Revenue + Order Count SELECT strftime('%Y', order_date) AS year, strftime('%m', order_date) AS month, SUM(amount) AS total_revenue, COUNT(DISTINCT order_id) AS total_orders FROM coffee_sales GROUP BY year, month ORDER BY year, month;

2️⃣ Revenue by Product (Coffee Type) SELECT product_id AS coffee_name, SUM(amount) AS total_revenue FROM coffee_sales GROUP BY coffee_name ORDER BY total_revenue DESC;

3️⃣ Top 3 Highest-Revenue Months SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS revenue FROM coffee_sales GROUP BY month ORDER BY revenue DESC LIMIT 3;

4️⃣ Daily Revenue (Optional) SELECT order_date, SUM(amount) AS daily_revenue FROM coffee_sales GROUP BY order_date ORDER BY order_date;

📄 Results

The results table (CSV or screenshot) is included in this repository:

📝 Key Insights

Monthly revenue patterns were identified through grouping and aggregation.

Strong differences in sales performance were seen across months.

Some coffee products generated significantly higher revenue.

Distinct trend peaks allowed identifying top-performing months.

🎤 Interview Questions Covered

The task helped answer:

How to group data by month and year?

Difference: COUNT(*) vs COUNT(DISTINCT col)

How to calculate monthly revenue?

What are SQL aggregate functions?

How to handle NULL values?

Purpose of GROUP BY and ORDER BY

How to get top 3 months by sales?

(these questions are listed in the task file on page 1)

task 6-1

📬 Submission

All required files have been added to GitHub as per submission guidelines (page 2)

task 6-1

✅ Task Completed Successfully!

If you want, I can also: ✔ Generate your SQL file ✔ Generate your results table ✔ Write answers for the interview questions ✔ Help you fix SQL errors