with bits as (
    select
    -- add 8 bits to the end to account for the delimiter
    bit_length(columns(*))  + 8
    from <TABLE>
),
-- aggregate all columns
bits_agg as (
    select sum(columns(*)) from bits
),
-- unpivot a wide single row of aggs to single column
bits_col as (
    unpivot bits_agg on columns(*)
)
-- add them all up & convert to mb
select sum(value) / (8 * 1024 ** 2) as mb
from bits_col
Copy code

CALL load_aws_credentials();
--- duckdb >= v0.9.0, AWS and httpfs extension loaded on the fly
Copy code

with 

nested_json_list as (
    
    select '["a", "b", "c"]'::json as foo

)
    
select json(foo)::varchar[] from nested_json_list
Copy code

-- Here we make use of list_transform and 
-- the fact that DuckDB knows lambda expressions
SELECT list_transform(split('01:08:22', ':'), x -> CAST (x AS INTEGER));
Copy code

-- * EXCLUDE plus any additional expression does the trick
SELECT * EXCLUDE (valid_from),  -- valid_from used in computation
       year(valid_from) AS year -- A new expression
FROM prices
 -- Note that the new expression can be referred to in the WHERE clause
WHERE year BETWEEN 2019 AND 2020;
Copy code

-- bar(x, min, max[, width])
--- Draw a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80.

with data as (select unnest(range(1,10)) as v order by random()) 

select bar(v,minv,maxv,30) from data, 
       (select min(v) as minv, max(v) as maxv from data);

-- ┌────────────────────────────────┐
-- │     bar(v, minv, maxv, 30)     │
-- │            varchar             │
-- ├────────────────────────────────┤
-- │ ██████████████████████▌        │
-- │ ███▊                           │
-- │ ██████████████████████████████ │
-- │                                │
-- │ ███████████████                │
-- │ ███████████▎                   │
-- │ ██████████████████▊            │
-- │ ██████████████████████████▎    │
-- │ ███████▌                       │
-- └────────────────────────────────┘
Copy code

-- duckdb has only weekday-name not number
-- create enum in the right order
create type Weekday as enum ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');

-- cast the string to enum for sorting
select count(*), cast(dayname(CreationDate) as Weekday) as day
from posts where posttypeid = 1 and tags like '%>sql<%'
group by all
order by day asc;
-- doing the cast in order by causes a bug

-- ┌──────────────┬───────────┐
-- │ count_star() │    day    │
-- │    int64     │  weekday  │
-- ├──────────────┼───────────┤
-- │       103937 │ Monday    │
-- │       115575 │ Tuesday   │
-- │       119825 │ Wednesday │
-- │       119514 │ Thursday  │
-- │       103445 │ Friday    │
-- │        47139 │ Saturday  │
-- │        47390 │ Sunday    │
-- └──────────────┴───────────┘
Copy code

select round(2061899,-6); -- 2000000.0
Copy code