Tables

VSCode

Tables are the default rendering for query results. While tables work without any tags, there are several options for controlling column display, table width, and nested data.

Properties

Property	Description	Example
`# table.size=fill`	Stretch table to fill container width	`# table.size=fill`
`# column { width }`	Set column width (`sm`, `md`, `lg`, or pixel value)	`# column { width=lg }`
`# column { word_break }`	Control text wrapping (`break_all`, `normal`)	`# column { word_break=break_all }`
`# flatten`	Flatten a nested record into parent columns	On `nest:` without `group_by`
`# hidden`	Hide a field from display	`# hidden`
`# label`	Override column header text	`# label="Display Name"`

The examples below all use the following semantic model.

source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: name is concat(code, ' - ', full_name)
  measure: airport_count is count()
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: orig is airports on origin = orig.code
  join_one: dest is airports on destination = dest.code
  join_one: carriers is duckdb.table('../data/carriers.parquet')
    on carrier = carriers.code
  measure: flight_count is count()
}

Column Width

Use the # column tag to control the width of individual columns. Width can be a preset (sm, md, lg) or a pixel value:

run: flights -> {
  group_by:
    carrier
    # column { width=lg }
    orig.name
  aggregate: flight_count
  limit: 10
}

QUERY RESULTS

[
  {
    "carrier": "AA",
    "name": "DFW - DALLAS/FORT WORTH INTERNATIONAL",
    "flight_count": 8742
  },
  {
    "carrier": "NW",
    "name": "MSP - MINNEAPOLIS-ST PAUL INTL/WOLD-CHAMBERLAIN/",
    "flight_count": 8662
  },
  {
    "carrier": "DL",
    "name": "ATL - THE WILLIAM B HARTSFIELD ATLANTA INTL",
    "flight_count": 8419
  },
  {
    "carrier": "EV",
    "name": "ATL - THE WILLIAM B HARTSFIELD ATLANTA INTL",
    "flight_count": 7392
  },
  {
    "carrier": "UA",
    "name": "ORD - CHICAGO O'HARE INTL",
    "flight_count": 6802
  },
  {
    "carrier": "NW",
    "name": "DTW - DETROIT METROPOLITAN WAYNE COUNTY",
    "flight_count": 6604
  },
  {
    "carrier": "WN",
    "name": "PHX - PHOENIX SKY HARBOR INTL",
    "flight_count": 6456
  },
  {
    "carrier": "US",
    "name": "CLT - CHARLOTTE/DOUGLAS INTL",
    "flight_count": 6427
  },
  {
    "carrier": "WN",
    "name": "LAS - MC CARRAN INTL",
    "flight_count": 6292
  },
  {
    "carrier": "MQ",
    "name": "DFW - DALLAS/FORT WORTH INTERNATIONAL",
    "flight_count": 6146
  }
]

SELECT 
   base."carrier" as "carrier",
   CONCAT(orig_0."code",' - ',orig_0."full_name") as "name",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN (SELECT GEN_RANDOM_UUID() as "__distinct_key", x.*  FROM '../data/airports.parquet' as x) AS orig_0
  ON base."origin"=orig_0."code"
GROUP BY 1,2
ORDER BY 3 desc NULLS LAST
LIMIT 10

Full Width Tables

By default, tables size to their content. Use # table.size=fill to stretch the table to fill its container:

# table.size=fill
run: flights -> {
  group_by: carriers.nickname
  aggregate: flight_count
  limit: 10
}

QUERY RESULTS

[
  {
    "nickname": "Southwest",
    "flight_count": 88751
  },
  {
    "nickname": "USAir",
    "flight_count": 37683
  },
  {
    "nickname": "American",
    "flight_count": 34577
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580
  },
  {
    "nickname": "United",
    "flight_count": 32757
  },
  {
    "nickname": "Delta",
    "flight_count": 32130
  },
  {
    "nickname": "Continental Express",
    "flight_count": 16074
  },
  {
    "nickname": "American Eagle",
    "flight_count": 15869
  },
  {
    "nickname": "Atlantic Southeast",
    "flight_count": 15769
  },
  {
    "nickname": "America West",
    "flight_count": 9750
  }
]

SELECT 
   carriers_0."nickname" as "nickname",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
 LEFT JOIN '../data/carriers.parquet' AS carriers_0
  ON base."carrier"=carriers_0."code"
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10

Nested Tables

Nested queries render as expandable sub-tables within each row:

run: flights -> {
  group_by: carriers.nickname
  aggregate: flight_count
  nest: top_destinations is {
    group_by: destination
    aggregate: flight_count
    limit: 5
  }
  limit: 5
}

QUERY RESULTS

[
  {
    "nickname": "Southwest",
    "flight_count": 88751,
    "top_destinations": [
      {
        "destination": "PHX",
        "flight_count": 6437
      },
      {
        "destination": "LAS",
        "flight_count": 6288
      },
      {
        "destination": "BWI",
        "flight_count": 5481
      },
      {
        "destination": "MDW",
        "flight_count": 4699
      },
      {
        "destination": "LAX",
        "flight_count": 4279
      }
    ]
  },
  {
    "nickname": "USAir",
    "flight_count": 37683,
    "top_destinations": [
      {
        "destination": "CLT",
        "flight_count": 6433
      },
      {
        "destination": "PHL",
        "flight_count": 5762
      },
      {
        "destination": "PIT",
        "flight_count": 4104
      },
      {
        "destination": "DCA",
        "flight_count": 2911
      },
      {
        "destination": "LGA",
        "flight_count": 1417
      }
    ]
  },
  {
    "nickname": "American",
    "flight_count": 34577,
    "top_destinations": [
      {
        "destination": "DFW",
        "flight_count": 8745
      },
      {
        "destination": "ORD",
        "flight_count": 5147
      },
      {
        "destination": "LAX",
        "flight_count": 1948
      },
      {
        "destination": "LGA",
        "flight_count": 1056
      },
      {
        "destination": "MIA",
        "flight_count": 1051
      }
    ]
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580,
    "top_destinations": [
      {
        "destination": "MSP",
        "flight_count": 8667
      },
      {
        "destination": "DTW",
        "flight_count": 6587
      },
      {
        "destination": "MEM",
        "flight_count": 1990
      },
      {
        "destination": "LAX",
        "flight_count": 990
      },
      {
        "destination": "MCO",
        "flight_count": 847
      }
    ]
  },
  {
    "nickname": "United",
    "flight_count": 32757,
    "top_destinations": [
      {
        "destination": "ORD",
        "flight_count": 6807
      },
      {
        "destination": "DEN",
        "flight_count": 4985
      },
      {
        "destination": "IAD",
        "flight_count": 2589
      },
      {
        "destination": "SFO",
        "flight_count": 2432
      },
      {
        "destination": "LAX",
        "flight_count": 2318
      }
    ]
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    carriers_0."nickname" as "nickname__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "flight_count__0",
    CASE WHEN group_set=1 THEN
      base."destination"
      END as "destination__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "flight_count__1"
  FROM '../data/flights.parquet' as base
   LEFT JOIN '../data/carriers.parquet' AS carriers_0
    ON base."carrier"=carriers_0."code"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2,4
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=0 THEN
                 ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY  "flight_count__0" desc) END  as __row_number__0,
  CASE WHEN GROUP_SET=1 THEN
                 ROW_NUMBER() OVER (PARTITION BY CAST("nickname__0" as VARCHAR), group_set ORDER BY  "flight_count__1" desc) END  as __row_number__1 
  FROM __stage0
)
, __stage2 AS (
  SELECT
    *,
    MAX(CASE WHEN group_set IN (0,1) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 5) THEN 1 ELSE 0 END
                 END) OVER(PARTITION BY CAST("nickname__0" as VARCHAR)) as __shaving__0 
  FROM __stage1 WHERE  NOT ((GROUP_SET = 1 AND __row_number__1 > 5))
)
, __stage3 AS (
  SELECT *
  FROM __stage2
  WHERE NOT ((group_set IN (0,1) AND __shaving__0 > 0)
  )
)
SELECT
  "nickname__0" as "nickname",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  COALESCE(LIST({
    "destination": "destination__1", 
    "flight_count": "flight_count__1"}  ORDER BY  "flight_count__1" DESC NULLS LAST) FILTER (WHERE group_set=1),[]) as "top_destinations"
FROM __stage3
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 5

Flattened Records

The # flatten tag collapses a nested record into the parent table as additional columns. This is useful for showing filtered aggregates side by side:

run: flights -> {
  group_by: carriers.nickname
  aggregate: flight_count
  nest:
    # flatten
    to_sfo is {
      aggregate: flight_count
      where: destination = 'SFO'
    }
    # flatten
    to_lax is {
      aggregate: flight_count
      where: destination = 'LAX'
    }
  limit: 10
}

QUERY RESULTS

[
  {
    "nickname": "Southwest",
    "flight_count": 88751,
    "to_sfo": {
      "flight_count": 86
    },
    "to_lax": {
      "flight_count": 4279
    }
  },
  {
    "nickname": "USAir",
    "flight_count": 37683,
    "to_sfo": {
      "flight_count": 156
    },
    "to_lax": {
      "flight_count": 136
    }
  },
  {
    "nickname": "American",
    "flight_count": 34577,
    "to_sfo": {
      "flight_count": 686
    },
    "to_lax": {
      "flight_count": 1948
    }
  },
  {
    "nickname": "Northwest",
    "flight_count": 33580,
    "to_sfo": {
      "flight_count": 520
    },
    "to_lax": {
      "flight_count": 990
    }
  },
  {
    "nickname": "United",
    "flight_count": 32757,
    "to_sfo": {
      "flight_count": 2432
    },
    "to_lax": {
      "flight_count": 2318
    }
  },
  {
    "nickname": "Delta",
    "flight_count": 32130,
    "to_sfo": {
      "flight_count": 132
    },
    "to_lax": {
      "flight_count": 337
    }
  },
  {
    "nickname": "Continental Express",
    "flight_count": 16074,
    "to_sfo": {
      "flight_count": null
    },
    "to_lax": {
      "flight_count": 1
    }
  },
  {
    "nickname": "American Eagle",
    "flight_count": 15869,
    "to_sfo": {
      "flight_count": null
    },
    "to_lax": {
      "flight_count": 25
    }
  },
  {
    "nickname": "Atlantic Southeast",
    "flight_count": 15769,
    "to_sfo": {
      "flight_count": null
    },
    "to_lax": {
      "flight_count": null
    }
  },
  {
    "nickname": "America West",
    "flight_count": 9750,
    "to_sfo": {
      "flight_count": 209
    },
    "to_lax": {
      "flight_count": 293
    }
  }
]

WITH __stage0 AS (
  SELECT
    group_set,
    carriers_0."nickname" as "nickname__0",
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "flight_count__0",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "flight_count__1",
    CASE WHEN group_set=2 THEN
      COUNT(1)
      END as "flight_count__2"
  FROM '../data/flights.parquet' as base
   LEFT JOIN '../data/carriers.parquet' AS carriers_0
    ON base."carrier"=carriers_0."code"
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND base."destination"='SFO')))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND base."destination"='LAX')))
  GROUP BY 1,2
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=0 THEN
                 ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY  "flight_count__0" desc) END  as __row_number__0 
  FROM __stage0
)
, __stage2 AS (
  SELECT
    *,
    MAX(CASE WHEN group_set IN (0,1,2) THEN CASE WHEN (GROUP_SET = 0 AND __row_number__0 > 10) THEN 1 ELSE 0 END
                 END) OVER(PARTITION BY CAST("nickname__0" as VARCHAR)) as __shaving__0 
  FROM __stage1 WHERE 1=1
)
, __stage3 AS (
  SELECT *
  FROM __stage2
  WHERE NOT ((group_set IN (0,1,2) AND __shaving__0 > 0)
  )
)
SELECT
  "nickname__0" as "nickname",
  MAX(CASE WHEN group_set=0 THEN "flight_count__0" END) as "flight_count",
  COALESCE(FIRST({"flight_count": "flight_count__1" }) FILTER(WHERE group_set=1), {"flight_count": NULL}) as "to_sfo",
  COALESCE(FIRST({"flight_count": "flight_count__2" }) FILTER(WHERE group_set=2), {"flight_count": NULL}) as "to_lax"
FROM __stage3
GROUP BY 1
ORDER BY 2 desc NULLS LAST
LIMIT 10