<commit_before>create temporary table lineitem using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Lineitem.parquet");
create temporary table orders using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Orders.parquet");
create temporary table customer using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Customer.parquet");
create temporary table supplier using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Supplier.parquet");
create temporary table partsupp using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Partsupp.parquet");
create temporary table region using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Region.parquet");
create temporary table nation using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Nation.parquet");
create temporary table part using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Part.parquet");

set spark.sql.codegen=true;
-- set spark.sql.autoBroadcastJoinThreshold=1073741824;
set spark.sql.parquet.filterPushdown=true;

-- Q1
select 
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
    sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= 19981201
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;


-- Q3
select
    l_orderkey,
    sum(l_extendedprice*(1-l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer join orders on c_custkey = o_custkey
    join lineitem on l_orderkey = o_orderkey
where
    c_mktsegment = 'HOUSEHOLD'
    and o_orderdate < 19950304
    and l_shipdate > 19950304
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

-- Q4
select
    o_orderpriority,
    count(*) as order_count
from
    orders join lineitem on l_orderkey = o_orderkey
where
    o_orderdate >= 19930801
    and o_orderdate < 19931101
    and l_commitdate < l_receiptdate
group by
    o_orderpriority
order by
    o_orderpriority;


-- Q5
select
    n_name,
    sum(l_extendedprice * (1-l_discount)) as revenue
from
    customer join orders on c_custkey = o_custkey
    join lineitem on l_orderkey = o_orderkey
    join supplier on l_suppkey = s_suppkey
    join nation on c_nationkey = n_nationkey
    join region on n_regionkey = r_regionkey
where
    c_nationkey = s_nationkey
    and r_name = 'ASIA'
    and o_orderdate >= 19960101
    and o_orderdate < 19970101
group by
    n_name
order by
    revenue desc;


-- Q6
select
    sum(l_extendedprice*l_discount) as revenue
from
    lineitem
where
    l_shipdate between 19960101 and 19970101
    and l_discount between 0.08 and 0.1
    and l_quantity < 24;


-- Q7
select
    supp_nation,
    cust_nation,
    l_year, sum(volume) as revenue
from (
    select
        n1.n_name as supp_nation,
        n2.n_name as cust_nation,
        l_shipdate/10000 as l_year,
        l_extendedprice * (1-l_discount) as volume
    from
        supplier join lineitem on s_suppkey = l_suppkey
        join orders on o_orderkey = l_orderkey
        join customer on c_custkey = o_custkey
        join nation as n1 on s_nationkey = n1.n_nationkey
        join nation as n2 on c_nationkey = n2.n_nationkey
    where
        ((n1.n_name = 'UNITED STATES' and n2.n_name = 'INDONESIA')
         or (n1.n_name = 'INDONESIA' and n2.n_name = 'UNITED STATES'))
         and l_shipdate between 19950101 and 19961231) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year;

-- Q8
select
    o_year,
    nation_volume / total_volume as mkt_share
from
    (select
        o_year,
        sum(case
            when nation = 'INDONESIA'
            then volume
            else 0 end) as nation_volume,
        sum(volume) as total_volume
    from (
        select
            o_orderdate/10000 as o_year,
            l_extendedprice * (1-l_discount) as volume,
            n2.n_name as nation
        from
            part join lineitem on p_partkey = l_partkey
            join supplier on s_suppkey = l_suppkey
            join orders on l_orderkey = o_orderkey
            join customer on o_custkey = c_custkey
            join nation n1 on c_nationkey = n1.n_nationkey
            join nation n2 on s_nationkey = n2.n_nationkey
            join region on n1.n_regionkey = r_regionkey
        where
            r_name = 'ASIA'
            and o_orderdate between 19950101 and 19961231
            and p_type = 'MEDIUM ANODIZED NICKEL'
        ) as all_nations
    group by
        o_year) as mkt
order by
    o_year;

-- Q9
select
    nation,
    o_year,
    sum(amount) as sum_profit
from (
    select
        n_name as nation,
        o_orderdate/10000 as o_year,
        l_extendedprice*(1-l_discount)-ps_supplycost * l_quantity as amount
    from
        lineitem join supplier on s_suppkey = l_suppkey
        join part on p_partkey = l_partkey
        join partsupp on ps_partkey = l_partkey and ps_suppkey = l_suppkey
        join orders on o_orderkey = l_orderkey
        join nation on s_nationkey = n_nationkey
    where
        p_name like '%ghost%'
    ) as profit
group by<commit_msg>Correctly extract year from date<commit_after>create temporary table lineitem using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Lineitem.parquet");
create temporary table orders using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Orders.parquet");
create temporary table customer using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Customer.parquet");
create temporary table supplier using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Supplier.parquet");
create temporary table partsupp using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Partsupp.parquet");
create temporary table region using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Region.parquet");
create temporary table nation using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Nation.parquet");
create temporary table part using org.apache.spark.sql.parquet options(path "hdfs://strong:9121/Part.parquet");

set spark.sql.codegen=true;
-- set spark.sql.autoBroadcastJoinThreshold=1073741824;
set spark.sql.parquet.filterPushdown=true;

-- Q1
select 
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
    sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= 19981201
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;


-- Q3
select
    l_orderkey,
    sum(l_extendedprice*(1-l_discount)) as revenue,
    o_orderdate,
    o_shippriority
from
    customer join orders on c_custkey = o_custkey
    join lineitem on l_orderkey = o_orderkey
where
    c_mktsegment = 'HOUSEHOLD'
    and o_orderdate < 19950304
    and l_shipdate > 19950304
group by
    l_orderkey,
    o_orderdate,
    o_shippriority
order by
    revenue desc,
    o_orderdate
limit 10;

-- Q4
select
    o_orderpriority,
    count(*) as order_count
from
    orders join lineitem on l_orderkey = o_orderkey
where
    o_orderdate >= 19930801
    and o_orderdate < 19931101
    and l_commitdate < l_receiptdate
group by
    o_orderpriority
order by
    o_orderpriority;


-- Q5
select
    n_name,
    sum(l_extendedprice * (1-l_discount)) as revenue
from
    customer join orders on c_custkey = o_custkey
    join lineitem on l_orderkey = o_orderkey
    join supplier on l_suppkey = s_suppkey
    join nation on c_nationkey = n_nationkey
    join region on n_regionkey = r_regionkey
where
    c_nationkey = s_nationkey
    and r_name = 'ASIA'
    and o_orderdate >= 19960101
    and o_orderdate < 19970101
group by
    n_name
order by
    revenue desc;


-- Q6
select
    sum(l_extendedprice*l_discount) as revenue
from
    lineitem
where
    l_shipdate between 19960101 and 19970101
    and l_discount between 0.08 and 0.1
    and l_quantity < 24;


-- Q7
select
    supp_nation,
    cust_nation,
    l_year, sum(volume) as revenue
from (
    select
        n1.n_name as supp_nation,
        n2.n_name as cust_nation,
        floor(l_shipdate/10000) as l_year,
        l_extendedprice * (1-l_discount) as volume
    from
        supplier join lineitem on s_suppkey = l_suppkey
        join orders on o_orderkey = l_orderkey
        join customer on c_custkey = o_custkey
        join nation as n1 on s_nationkey = n1.n_nationkey
        join nation as n2 on c_nationkey = n2.n_nationkey
    where
        ((n1.n_name = 'UNITED STATES' and n2.n_name = 'INDONESIA')
         or (n1.n_name = 'INDONESIA' and n2.n_name = 'UNITED STATES'))
         and l_shipdate between 19950101 and 19961231) as shipping
group by
    supp_nation,
    cust_nation,
    l_year
order by
    supp_nation,
    cust_nation,
    l_year;

-- Q8
select
    o_year,
    nation_volume / total_volume as mkt_share
from
    (select
        o_year,
        sum(case
            when nation = 'INDONESIA'
            then volume
            else 0 end) as nation_volume,
        sum(volume) as total_volume
    from (
        select
            floor(o_orderdate/10000) as o_year,
            l_extendedprice * (1-l_discount) as volume,
            n2.n_name as nation
        from
            part join lineitem on p_partkey = l_partkey
            join supplier on s_suppkey = l_suppkey
            join orders on l_orderkey = o_orderkey
            join customer on o_custkey = c_custkey
            join nation n1 on c_nationkey = n1.n_nationkey
            join nation n2 on s_nationkey = n2.n_nationkey
            join region on n1.n_regionkey = r_regionkey
        where
            r_name = 'ASIA'
            and o_orderdate between 19950101 and 19961231
            and p_type = 'MEDIUM ANODIZED NICKEL'
        ) as all_nations
    group by
        o_year) as mkt
order by
    o_year;

-- Q9
select
    nation,
    o_year,
    sum(amount) as sum_profit
from (
    select
        n_name as nation,
        floor(o_orderdate/10000) as o_year,
        l_extendedprice*(1-l_discount)-ps_supplycost * l_quantity as amount
    from
        lineitem join supplier on s_suppkey = l_suppkey
        join part on p_partkey = l_partkey
        join partsupp on ps_partkey = l_partkey and ps_suppkey = l_suppkey
        join orders on o_orderkey = l_orderkey
        join nation on s_nationkey = n_nationkey
    where
        p_name like '%ghost%'
    ) as profit
group by
{'fraction_non_alphanumeric': 0.06784285194616224, 'fraction_numerical': 0.03692251727901055, 'mean_word_length': 2.814429413805064, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7570418', 'n_tokens_mistral': 4046, 'n_tokens_neox': 3742, 'n_words': 997}