FrançaisGet started

Transforming data for analysis

Six table transformations written in SQL: filtering, deriving a column, aggregating to a grain, joining, pivoting and binning; their order and the number of rows each one returns.

  • 24 cards
  • 6 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. The numbers
  5. Scenarios
  6. Understand

A few cards from the deck

  • Data transformation: what is it?

    Show the answer

    An operation that builds, from one table, another table closer to the question asked: you filter, compute, group, join, pivot.

    Order matters: filtering out cancelled orders before aggregating gives a different total than aggregating then filtering.

  • With the six orders below, which row appears in the query result?

    ```sql
    -- cmd(id, region, mois, montant, statut)
    -- (1, Nord, jan, 100, ok) (4, Sud, fev, 70, ok)
    -- (2, Nord, jan, 50, ok) (5, Nord, fev, 30, ok)
    -- (3, Sud, jan, 80, annulee) (6, Sud, jan, 20, ok)
    SELECT region, mois, COUNT(*) AS n, SUM(montant) AS total
    FROM cmd
    WHERE statut = 'ok'
    GROUP BY region, mois;
    `

    • Nord | jan | 2 | 150
    • Nord | jan | 3 | 150
    • Nord | jan | 2 | 75
    • Sud | jan | 2 | 100
    Show the answer

    Nord | jan | 2 | 150

    The filter comes before the grouping: order 3, cancelled, does not count, and South in January is only 20 on one row. North in January groups orders 1 and 2: 2 rows, 150. The result has 4 rows.

  • … filters rows before the grouping, whereas … filters the groups after the aggregates are computed.

    Show the answer

    WHERE filters rows before the grouping, whereas HAVING filters the groups after the aggregates are computed.

    A condition on a total (SUM(montant) > 1000) only exists after grouping: it goes in HAVING. A condition on a row's column goes in WHERE, and lightens the grouping work.

  • GROUP BY: what is it?

    Show the answer

    The SQL clause that groups rows holding the same values in the listed columns and returns one row per group present in the data.

    A combination absent from the data (a product never sold on a Sunday) produces no row: it does not appear with a zero.

Study this deck