🎬 SQL Data Analytics & Backend Pipeline

Status: Portfolio Project | Domain: Data Engineering, Backend, SQL

Project Overview

This repository demonstrates a structured, modular Python backend application interacting with a relational SQL database.

It goes beyond simple data retrieval by implementing a Custom Analytics Tracking System (Logging engine) that records user search behavior and generates real-time "Top 10 Popular Queries" reports.

Technical Highlights

1. Modular Architecture

The project is strictly decoupled to follow software engineering best practices:

• main.py: Application entry point.
• db.py: Database connection and session management.
• sql_queries.py: Centralized repository for all SQL statements.
• ui.py: User Interface / CLI logic.
• my_exceptions.py & user_exceptions.py: Custom error handling and graceful degradation logic.

2. Advanced SQL Implementation

The codebase demonstrates secure and efficient database operations:

• Parameterized Queries (%s): 100% protection against SQL Injection attacks.
• Complex JOINs: Multi-table aggregations (e.g., joining film, film_actor, and actor tables for deep filtering).
• Upsert Pattern (Analytics Log): Utilizes ON DUPLICATE KEY UPDATE to track search query frequencies and build a live telemetry table without overwriting data.

Business Logic Examples (from sql_queries.py)

• Dynamic Search: Filtering films by Actor's name, Category, and Release Year using LOWER() and CONCAT() functions.
• Telemetry Engine: Automatically incrementing a counter (execution_count) every time a user performs a specific search, providing raw data for BI dashboards.

---

Developed by Anna Gridasova