SQL for Data Scientists: Advanced Queries

SQL: The Language of Data

No matter how good you are at Python and ML, you'll spend significant time in SQL—the language for querying databases. At this level, you move beyond basic SELECT/WHERE to advanced techniques that real data scientists use. Window functions are game-changers: they compute values over a sliding window of rows without collapsing them into groups. ROW_NUMBER() assigns a rank to each row within a partition; RANK() gives the same rank to ties. LAG() and LEAD() access previous and next rows—useful for computing changes over time. SUM() OVER (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) computes a 30-day rolling sum. These calculations are far faster in SQL than loading into Python. Common Table Expressions (CTEs) using WITH let you break complex queries into readable chunks, improving maintainability. You can reference a CTE multiple times, avoiding repetition. Recursive CTEs even let you traverse hierarchies (organizational charts, file systems). Self-joins compare rows in the same table: find pairs of customers who bought the same product. GROUP_CONCAT concatenates values; CTEs with UNION build complex pipelines. Subqueries (queries within queries) filter data at intermediate stages. EXISTS checks if a condition is true for any row. CASE statements implement conditional logic—create a feature column with different values based on conditions. String functions (SUBSTRING, CONCAT, UPPER, LOWER) clean text. Date functions (DATE_DIFF, DATE_ADD) handle temporal data. CAST converts between types. Aggregate functions (COUNT DISTINCT, PERCENTILE_CONT) compute distributions. Query optimization is crucial: indexes on frequently-filtered columns speed up queries, EXPLAIN shows execution plans. NULL handling matters: NULL is not zero, COUNT(*) vs COUNT(column) differ. You'll write queries to engineer features directly in SQL (faster than Python), join multiple tables, handle missing values, and compute statistics. Real datasets live in databases; SQL is how you access and prepare them efficiently.

From Concept to Reality: SQL for Data Scientists: Advanced Queries

In the professional world, the difference between a good engineer and a great one often comes down to understanding fundamentals deeply. Anyone can copy code from Stack Overflow. But when that code breaks at 2 AM and your application is down — affecting millions of users — only someone who truly understands the underlying concepts can diagnose and fix the problem.

SQL for Data Scientists: Advanced Queries is one of those fundamentals. Whether you end up working at Google, building your own startup, or applying CS to solve problems in agriculture, healthcare, or education, these concepts will be the foundation everything else is built on. Indian engineers are known globally for their strong fundamentals — this is why companies worldwide recruit from IITs, NITs, IIIT Hyderabad, and BITS Pilani. Let us make sure you have that same strong foundation.

Database Design: Normalisation and Relationships

Good database design prevents data duplication and inconsistency. This is called normalisation. Consider an e-commerce database:

-- BAD design (denormalised — data repeated everywhere)
-- If customer moves city, you must update EVERY order row!

-- GOOD design (normalised — each fact stored once)
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city  TEXT
);

CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    price DECIMAL(10,2),
    category TEXT
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product_id  INTEGER REFERENCES products(id),
    quantity    INTEGER,
    order_date  TIMESTAMP DEFAULT NOW()
);

-- JOIN to reconstruct the full picture
SELECT c.name, p.name AS product, o.quantity,
       (p.price * o.quantity) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.order_date > '2025-01-01';

The REFERENCES keyword creates a foreign key — a link between tables. This is a relational database: data is stored in related tables, and JOINs combine them. The tradeoff: normalised databases are consistent and space-efficient, but JOINs can be slow on very large datasets. This is why companies like Flipkart use a mix of SQL databases (for transactions) and NoSQL databases like MongoDB or Cassandra (for product catalogs and recommendations).

Real-World System Design: Swiggy's Architecture

When you order food on Swiggy, here is what happens behind the scenes in about 2 seconds: your location is geocoded (algorithms), nearby restaurants are queried from a spatial index (data structures), menu prices are pulled from a database (SQL), delivery time is estimated using ML models trained on historical data (AI), the order is placed in a distributed message queue (Kafka), a delivery partner is assigned using a matching algorithm (optimization), and real-time tracking begins using WebSocket connections (networking). EVERY concept in your CS curriculum is being used simultaneously to deliver your biryani.

Algorithm Complexity and Big-O Notation

Big-O notation describes how an algorithm's performance scales with input size. This is THE most important concept for coding interviews:

  BIG-O COMPARISON (n = 1,000,000 elements):

  O(1)        Constant     1 operation          Hash table lookup
  O(log n)    Logarithmic  20 operations        Binary search
  O(n)        Linear       1,000,000 ops        Linear search
  O(n log n)  Linearithmic 20,000,000 ops       Merge sort, Quick sort
  O(n²)       Quadratic    1,000,000,000,000    Bubble sort, Selection sort
  O(2ⁿ)       Exponential  ∞ (universe dies)    Brute force subset

  Time at 1 billion ops/sec:
  O(n log n): 0.02 seconds    ← Perfectly usable
  O(n²):      11.5 DAYS       ← Completely unusable!
  O(2ⁿ):      Longer than the age of the universe

  # Python example: Merge Sort (O(n log n))
  def merge_sort(arr):
      if len(arr) <= 1:
          return arr
      mid = len(arr) // 2
      left = merge_sort(arr[:mid])      # Sort left half
      right = merge_sort(arr[mid:])     # Sort right half
      return merge(left, right)         # Merge sorted halves

  def merge(left, right):
      result = []
      i = j = 0
      while i < len(left) and j < len(right):
          if left[i] <= right[j]:
              result.append(left[i]); i += 1
          else:
              result.append(right[j]); j += 1
      result.extend(left[i:])
      result.extend(right[j:])
      return result

This matters in the real world. India's Aadhaar system must search through 1.4 billion biometric records for every authentication request. At O(n), that would take seconds per request. With the right data structures (hash tables, B-trees), it takes milliseconds. The algorithm choice is the difference between a working system and an unusable one.

Production Engineering: SQL for Data Scientists: Advanced Queries at Scale

Understanding sql for data scientists: advanced queries at an academic level is necessary but not sufficient. Let us examine how these concepts manifest in production environments where failure has real consequences.

Consider India's UPI system processing 10+ billion transactions monthly. The architecture must guarantee: atomicity (a transfer either completes fully or not at all — no half-transfers), consistency (balances always add up correctly across all banks), isolation (concurrent transactions on the same account do not interfere), and durability (once confirmed, a transaction survives any failure). These are the ACID properties, and violating any one of them in a payment system would cause financial chaos for millions of people.

At scale, you also face the thundering herd problem: what happens when a million users check their exam results at the same time? (CBSE result day, anyone?) Without rate limiting, connection pooling, caching, and graceful degradation, the system crashes. Good engineering means designing for the worst case while optimising for the common case. Companies like NPCI (the organisation behind UPI) invest heavily in load testing — simulating peak traffic to identify bottlenecks before they affect real users.

Monitoring and observability become critical at scale. You need metrics (how many requests per second? what is the 99th percentile latency?), logs (what happened when something went wrong?), and traces (how did a single request flow through 15 different microservices?). Tools like Prometheus, Grafana, ELK Stack, and Jaeger are standard in Indian tech companies. When Hotstar streams IPL to 50 million concurrent users, their engineering team watches these dashboards in real-time, ready to intervene if any metric goes anomalous.

The career implications are clear: engineers who understand both the theory (from chapters like this one) AND the practice (from building real systems) command the highest salaries and most interesting roles. India's top engineering talent earns ₹50-100+ LPA at companies like Google, Microsoft, and Goldman Sachs, or builds their own startups. The foundation starts here.

Key Vocabulary

Here are important terms from this chapter that you should know:

💡 Interview-Style Problem

Here is a problem that frequently appears in technical interviews at companies like Google, Amazon, and Flipkart: "Design a URL shortener like bit.ly. How would you generate unique short codes? How would you handle millions of redirects per second? What database would you use and why? How would you track click analytics?"

Think about: hash functions for generating short codes, read-heavy workload (99% redirects, 1% creates) suggesting caching, database choice (Redis for cache, PostgreSQL for persistence), and horizontal scaling with consistent hashing. Try sketching the system architecture on paper before looking up solutions. The ability to think through system design problems is the single most valuable skill for senior engineering roles.

Where This Takes You

The knowledge you have gained about sql for data scientists: advanced queries is directly applicable to: competitive programming (Codeforces, CodeChef — India has the 2nd largest competitive programming community globally), open-source contribution (India is the 2nd largest contributor on GitHub), placement preparation (these concepts form 60% of technical interview questions), and building real products (every startup needs engineers who understand these fundamentals).

India's tech ecosystem offers incredible opportunities. Freshers at top companies earn ₹15-50 LPA; experienced engineers at FAANG companies in India earn ₹50-1 Cr+. But more importantly, the problems being solved in India — digital payments for 1.4 billion people, healthcare AI for rural areas, agricultural tech for 150 million farmers — are some of the most impactful engineering challenges in the world. The fundamentals you are building will be the tools you use to tackle them.

Crafted for Class 7–9 • Data Science • Aligned with NEP 2020 & CBSE Curriculum