Thomas BrochardDevelopment · Data · AI
Let’s talk
Back to selected work

Analytics & ML

Case study / 08

Real Estate SQL Analytics

Preserve transaction grain through complex joins.

A normalized transaction model for geographic comparisons, temporal analysis and grain-aware SQL queries.

Technologies & concepts

  • SQL
  • SQLite
  • Relational modelling
  • CTEs
  • Window functions

Technical demonstration / selected source

Private repository

01 / The context

The problem behind the design.

Real-estate transactions link geography, addresses, buyers and multiple property lots. Joining these relationships without preserving transaction grain can multiply values and distort geographic or temporal summaries.

02 / Engineering decisions

Choices made explicit.

Normalize the relationships

Seven tables separate geographic references, addresses, buyer references, transactions and lots. Foreign keys, constrained measures and indexes document their relationships.

Aggregate at transaction grain

Regional and monthly totals are computed before lot-level comparison. Transaction values repeated in the lot-context view are treated as non-additive.

Bound price comparisons

Price-per-area examples use single-lot transactions. Orphan-reference and missing-value queries expose data-fitness questions before interpretation.

03 / System view

Follow the flow.

Real-estate SQL design: geographic references connect to a transaction model and lot relationships before grain-aware analytical queries and quality checks.

Illustrative architecture from the project presentation. It describes the selected design; it does not imply a deployed service or a measured operational result.

View the full-size diagram
  1. Geographic reference

  2. Transaction model

  3. Lot relationships

  4. Analytical queries

  5. Quality checks

04 / Selected artifacts

What the repository contains.

  • sql/schema.sql
  • sql/analytics.sql
  • sql/quality_checks.sql

05 / Scope & limitations

How to read this project.

Demonstration and source showcase with SQL source but no transaction data or database. Price-per-area examples cover single-lot transactions only; multi-lot allocation needs a separate rule. Minimum sample-size filters are illustrative choices rather than statistical guarantees. No buyer names, real addresses or observed market results are included.

Explore the source with access.

The GitHub repository is private. Its link works for authorized collaborators; other visitors can contact me to discuss the project.

Next case study / 09

Cultural Events RAG

Retrieval systems

Explore the collection

The next conversation

Let’s talk about your project.

↗
A need, an idea, a technical question.
LinkedIn ↗GitHub ↗