framework/resources/Datasources/ctas/create_tables_parquet.ddl

alter session set `store.format` = 'parquet';

create table dfs.ctas_parquet.ctas_t1(c1, c2, c3, c4) as
select  j1.c_varchar,
        j2.c_varchar,
        j1.c_date,
        j2.c_date
from
        dfs.`ctas`.`j1` inner join dfs.`ctas`.`j2` ON (j1.c_timestamp = j2.c_timestamp and j1.c_date between '1960-01-03' and '1960-01-15' )
where
        j1.c_bigint IS NOT DISTINCT FROM j2.c_bigint
        and j1.c_timestamp IS NOT NULL
;

create table dfs.ctas_parquet.ctas_t2(c1, c2) as
select  
        j1.c_bigint,
        coalesce(j3.c_bigint, -1000)
from    
        dfs.`ctas`.`j1` left outer join dfs.`ctas`.`j3` ON (j1.c_time = j3.c_time)
where   
        j1.c_boolean IS NULL
        and j1.d9 > 0
;      

create table dfs.ctas_parquet.ctas_t3 as
select  j4.c_varchar || j2.c_varchar as c1
from    
        dfs.`ctas`.`j4` right outer join dfs.`ctas`.`j2` on (j2.c_integer = j4.c_integer)
;  

create table dfs.ctas_parquet.ctas_t4 as
select  concat(j4.c_varchar, j2.c_varchar) as c1
from    
        dfs.`ctas`.`j4` right outer join dfs.`ctas`.`j2` on (j2.c_integer = j4.c_integer)
;   

create table dfs.ctas_parquet.ctas_t5(count_star, max_j1_c_int, min_j2_c_int, avg_j1_c_int, avg_j2_c_int) as
select  
        count(*),
        max(j1.c_integer),
        min(j2.c_integer),
        avg(j1.c_integer),
        avg(j2.c_integer)
from    
        dfs.`ctas`.`j1` full outer join dfs.`ctas`.`j2` on (j1.c_date = j2.c_date)
where   
        j1.c_boolean is false
;

-- create table ctas_t6(c1,c2,c3,c4,c5) as select c_integer, c_bigint, c_date, c_time, c_varchar from dfs.`ctas`.`j4` where c_bigint is null;

create table dfs.ctas_parquet.ctas_t8(c1) as select distinct c_integer from ( select c_integer from dfs.`ctas`.`j1` union all select c_integer from dfs.`ctas`.`j2`) as xyz;

create table dfs.ctas_parquet.ctas_t9 as
select  c_integer          as c1,
        count(c_date)      as c2 
from    
        dfs.`ctas`.`j1`
group by 
        c_integer
order by 
        c_integer;


create table dfs.ctas_parquet.ctas_t10 (c1, c2) as
select  
        count(distinct c_integer),
        sum(c_integer)
from    
        dfs.`ctas`.`j1`
group by 
        c_date
order by 
        c_date;

create table dfs.ctas_parquet.ctas_t11 as
select  
        count(distinct d9) as count_distinct_d9, 
        sum(d9)            as sum_d9 
from    
        (
        select
                d9,
                c_date,
                c_timestamp
        from    dfs.`ctas`.`j3`
        ) as sq 
group by 
        c_date 
order by 
        c_date,
        sum(d9) desc nulls first;

create table dfs.ctas_parquet.ctas_t12 (c1, c2, c3) as
select
        count(distinct c_bigint) ,
        sum(c_bigint)            ,
        count(c_date)            
from
        dfs.`ctas`.`j4`
group by
        c_boolean,
        c_timestamp
order by
        2
;

create table dfs.ctas_parquet.ctas_t13 (c1, c2)  as
select  
        count(distinct d18),
        sum(d18) 
from    
        (
        select  distinct 
                d18,
                c_date, 
                c_varchar  
        from
                dfs.`ctas`.`j1`
        ) as sq
group by 
        c_varchar;

create table dfs.ctas_parquet.ctas_t14(x, y) as
select  
        c_date,
        count(distinct c_time)
from    
        dfs.`ctas`.`j4`
group by 
        c_date  
order by 
        c_date limit 10 offset 900;

create table dfs.ctas_parquet.ctas_t15 as
select  
        count(distinct c_integer) as count_distinct_1,
        count(distinct c_varchar) as count_distinct_2,
        count(distinct c_time) as count_distinct_3
from    
        dfs.`ctas`.`j1`
group by 
        c_time
;