🔍 BigQuery SQL Analytics Project

📋 Overview

This repository contains SQL scripts converted for use with Google BigQuery. The scripts demonstrate various data analytics techniques and reporting methods for business intelligence applications.

🔑 Key Features

• 📊 Database Exploration: Examine database structure and metadata
• 🧩 Dimensions Analysis: Explore categorical data and hierarchies
• 📅 Date Range Analysis: Analyze temporal boundaries and trends
• 📏 Magnitude Analysis: Quantify data across dimensions
• 🏆 Ranking Analysis: Identify top performers and outliers
• 📈 Change Over Time: Track growth and trends across periods
• 💹 Cumulative Analysis: Calculate running totals and moving averages
• 🎯 Performance Analysis: Benchmark against historical data
• 🧮 Data Segmentation: Group data into meaningful categories
• 📊 Part-to-Whole Analysis: Understand proportional relationships
• 👥 Customer Reporting: Generate comprehensive customer insights
• 🛍️ Product Reporting: Analyze product performance metrics

🗂️ Project Structure

bigquery-sql-analytics/
├── 📁 scripts/            # BigQuery SQL scripts
├── 📁 data/               # Sample CSV data files
├── 📁 docs/               # Documentation files
│   └── 📁 images/         # Images for documentation
├── 📄 README.md           # Project documentation
└── 📄 .gitignore          # Git ignore configuration

📊 Data Files

The repository includes the following sample data files:

File	Description
👤 gold.dim_customers.csv	Customer dimension data
🛍️ gold.dim_products.csv	Product dimension data
💰 gold.fact_sales.csv	Sales fact data
📊 gold.report_customers.csv	Pre-generated customer report
📈 gold.report_products.csv	Pre-generated product report

🚀 Getting Started

Prerequisites

• Google Cloud Platform account with BigQuery access
• BigQuery Studio or other SQL client
• Basic knowledge of SQL and data analytics

💾 Loading Data

To load the data into BigQuery:

1. Create a dataset in BigQuery

   CREATE SCHEMA `your-project.data_warehouse_analytics`;

2. Upload the CSV files using one of these methods:

   • BigQuery web UI: Navigate to your dataset → "Create Table" → Select file
   • bq command-line tool:

     bq load \
       --source_format=CSV \
       --skip_leading_rows=1 \
       your-project:data_warehouse_analytics.dim_customers \
       ./data/gold.dim_customers.csv \
       schema_definition

3. Run the scripts in the /scripts directory in sequence

📝 Conversion Notes

These scripts have been converted from standard SQL to BigQuery SQL, with the following key changes:

Standard SQL	BigQuery SQL
DATETRUNC	DATE_TRUNC
DATEDIFF	DATE_DIFF
CAST AS FLOAT	CAST AS FLOAT64
NULLIF(x, 0)	SAFE_DIVIDE
TOP N	LIMIT N
schema.table	project.dataset.table
GETDATE()	CURRENT_DATE()

📚 Documentation

Each SQL script includes detailed comments explaining:

• Purpose of the analysis
• SQL functions and techniques used
• Expected insights and applications

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

---

📊 Happy Data Analyzing! 📈