🛝SQL Diagram

The SQL Diagram feature is a sandbox tool that allows you to visualize lineage and relationships from a certain SQL query.

It helps you:

• understand the CTEs flow when I comes to a complex query that has 1,000+ lines of codes.

• tell a story of which JOINs are used in your query.

• automatically create database diagrams and export them into SVG or PNG.

Check out our SQL diagram blog series on Medium:

https://medium.com/gitconnected/sql-diagram-part-4-query-interpretation-understand-complex-sql-visually-c1bd045f7075

SQL CTEs to Lineage Diagram (DAG)

SQL to ER Diagram

Code

WITH space_station_residencies AS (
    SELECT
        ssr.residency_id,
        ssr.start_date,
        ssr.end_date,
        ss.space_station_id,
        ss.station_name,
        a.astronaut_id,
        a.astronaut_name,
        a.nationality
    FROM `station_residencies` ssr
    JOIN `space_stations` ss ON ssr.space_station_id = ss.space_station_id
    JOIN `astronaut_residencies` ar ON ssr.residency_id = ar.residency_id
    JOIN `astronauts` a ON ar.astronaut_id = a.astronaut_id
),
residency_summary AS (
    SELECT
        sr.station_name,
        sr.nationality,
        COUNT(DISTINCT sr.astronaut_id) AS total_astronauts,
        COUNT(DISTINCT sr.residency_id) AS total_residencies,
        AVG(DATE_DIFF(sr.end_date, sr.start_date, DAY)) AS avg_duration
    FROM
        space_station_residencies sr
    GROUP BY
        station_name,
        nationality
)
SELECT *
FROM residency_summary
ORDER BY station_name ASC, total_residencies DESC;

---

What's next:

Using DDLs to define table schema:

DDL to ERD

• CREATE TABLE statement for tables

• CREATE TEMPORARY TABLE statement for CTEs

CREATE TABLE astronauts (
    astronaut_name VARCHAR
    ...
);

CREATE TEMPORARY TABLE residency_summary (
    total_astronauts INT,
    total_residencies INT,
    avg_duration FLOAT
);

CREATE TEMPORARY TABLE space_station_residencies (
    residency_id VARCHAR,
    start_date DATE,
    end_date DATE,
    space_station_id VARCHAR,
    astronaut_id VARCHAR,
    station_name VARCHAR,
    nationality VARCHAR
);