# Single Table Performance Testing Statements

## Based on Star Schema Benchmark Tool

- #### Q1.1

  ```sql
  SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  FROM lineorder_flat
  WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;
  ```

- #### Q1.2

  ```sql
  SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  FROM lineorder_flat
  WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;
  ```

- #### Q1.3

  ```sql
  SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
  FROM lineorder_flat
  WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994
    AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;
  ```

- #### Q2.1

  ```sql
  SELECT
      sum(LO_REVENUE),
      toYear(LO_ORDERDATE) AS year,
      P_BRAND
  FROM lineorder_flat
  WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
  GROUP BY
      year,
      P_BRAND
  ORDER BY
      year,
      P_BRAND;
  ```

- #### Q2.2

  ```sql
  SELECT
      sum(LO_REVENUE),
      toYear(LO_ORDERDATE) AS year,
      P_BRAND
  FROM lineorder_flat
  WHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'
  GROUP BY
      year,
      P_BRAND
  ORDER BY
      year,
      P_BRAND;
  ```

- #### Q2.3

  ```sql
  SELECT
      sum(LO_REVENUE),
      toYear(LO_ORDERDATE) AS year,
      P_BRAND
  FROM lineorder_flat
  WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'
  GROUP BY
      year,
      P_BRAND
  ORDER BY
      year,
      P_BRAND;
  ```

- #### Q3.1

  ```sql
  SELECT
      C_NATION,
      S_NATION,
      toYear(LO_ORDERDATE) AS year,
      sum(LO_REVENUE) AS revenue
  FROM lineorder_flat
  WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND year >= 1992 AND year <= 1997
  GROUP BY
      C_NATION,
      S_NATION,
      year
  ORDER BY
      year ASC,
      revenue DESC;
  ```

- #### Q3.2

  ```sql
  SELECT
      C_CITY,
      S_CITY,
      toYear(LO_ORDERDATE) AS year,
      sum(LO_REVENUE) AS revenue
  FROM lineorder_flat
  WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997
  GROUP BY
      C_CITY,
      S_CITY,
      year
  ORDER BY
      year ASC,
      revenue DESC;
  ```

- #### Q3.3

  ```sql
  SELECT
      C_CITY,
      S_CITY,
      toYear(LO_ORDERDATE) AS year,
      sum(LO_REVENUE) AS revenue
  FROM lineorder_flat
  WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997
  GROUP BY
      C_CITY,
      S_CITY,
      year
  ORDER BY
      year ASC,
      revenue DESC;
  ```

- #### Q3.4

  ```sql
  SELECT
      C_CITY,
      S_CITY,
      toYear(LO_ORDERDATE) AS year,
      sum(LO_REVENUE) AS revenue
  FROM lineorder_flat
  WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5') AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = 199712
  GROUP BY
      C_CITY,
      S_CITY,
      year
  ORDER BY
      year ASC,
      revenue DESC;
  ```

- #### Q4.1

  ```sql
  SELECT
      toYear(LO_ORDERDATE) AS year,
      C_NATION,
      sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
  FROM lineorder_flat
  WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
  GROUP BY
      year,
      C_NATION
  ORDER BY
      year ASC,
      C_NATION ASC;
  ```

- #### Q4.2

  ```sql
  SELECT
      toYear(LO_ORDERDATE) AS year,
      S_NATION,
      P_CATEGORY,
      sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
  FROM lineorder_flat
  WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2')
  GROUP BY
      year,
      S_NATION,
      P_CATEGORY
  ORDER BY
      year ASC,
      S_NATION ASC,
      P_CATEGORY ASC;
  ```

- #### Q4.3

  ```sql
  SELECT
      toYear(LO_ORDERDATE) AS year,
      S_CITY,
      P_BRAND,
      sum(LO_REVENUE - LO_SUPPLYCOST) AS profit
  FROM lineorder_flat
  WHERE S_NATION = 'UNITED STATES' AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14'
  GROUP BY
      year,
      S_CITY,
      P_BRAND
  ORDER BY
      year ASC,
      S_CITY ASC,
      P_BRAND ASC;
  ```