-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
----------------------------------------------------
-- TEST012
-- Unit Test to verify Sony EAP queries
-- Queries and MVs were provided by Sony
-- Author: Taoufik Ben Abdellatif
-- Owner: MV Query Rewrite team
----------------------------------------------------

obey TEST012(clean_up);
log LOG012 clear;

obey TEST012(set_up);
obey TEST012(create_tables);

obey TEST012(indirect_group_by);
obey TEST012(back_joins);
obey TEST012(extra_hub_tables);
obey TEST012(range_predicates);
obey TEST012(rollup);
obey TEST012(user_playtime_dem_agg);
obey TEST012(user_playtime_acct_agg);

obey TEST012(clean_up);
exit;

?section clean_up
----------------------------------------------------
------------------ clean up section ----------------
----------------------------------------------------
drop schema sony_test cascade;

?section set_up
----------------------------------------------------
-------------------- set up section ----------------
----------------------------------------------------

control query default MVQR_REWRITE_LEVEL '3';
control query default MVQR_PUBLISH_TO 'PRIVATE';

create schema sony_test;
set schema sony_test;

prepare checkPlan from
  select tname
  from table(explain(NULL, 'QUERYSTMT'))
  where operator like '%_SCAN%'
  order by tname;

?section create_tables
--===========================================
---------- create tables section ------------
--===========================================

CREATE TABLE DIM_ACCT
(
        acct_id               LARGEINT  NOT NULL ,
        demographic_id        SMALLINT  NOT NULL ,
        source_psn_acct_id    LARGEINT ,
        source_psn_acct_hex   CHAR(16) ,
        source_master_psn_acct_id  LARGEINT  NOT NULL ,
        source_online_id      VARCHAR(50)  NOT NULL ,
        vulgar_source_online_id_ind  CHAR(1)  NOT NULL ,
        source_acct_jid_id    VARCHAR(100) ,
        auto_deposit_ind      CHAR(1)  NOT NULL ,
        adult_confirmed_ind   CHAR(1)  NOT NULL ,
        account_suspend_ind   CHAR(1)  NOT NULL ,
        email_confirmed_ind   CHAR(1)  NOT NULL ,
        dob                   DATE ,
        gender                CHAR(15)  NOT NULL ,
        language_iso_code     CHAR(2)  NOT NULL ,
        language_name         VARCHAR(30)  NOT NULL ,
        country_iso_code      CHAR(2)  NOT NULL ,
        country_name          VARCHAR(30)  NOT NULL ,
        time_zone_iso_code    CHAR(3) ,
        acct_creation_dt_id   INTEGER  NOT NULL ,
        acct_creation_utc_dttm  TIMESTAMP(0)  NOT NULL ,
        acct_updated_dt_id    INTEGER  NOT NULL ,
        acct_updated_utc_dttm  TIMESTAMP(0)  NOT NULL ,
        utc_offset            SMALLINT ,
        acct_creation_local_time_id  INTEGER  NOT NULL ,
        acct_updated_local_time_id  INTEGER  NOT NULL ,
        optin_status_direct   CHAR(1)  NOT NULL ,
        optin_status_3rd_party  CHAR(1)  NOT NULL ,
        source_sce_region_code  CHAR(10)  NOT NULL ,
        sce_region_code       CHAR(10)  NOT NULL ,
        signup_console_code   VARCHAR(64)  default NULL ,
        eula_version          NUMERIC(9) ,
        source_system_id      SMALLINT  NOT NULL ,
        scd_start_dt          DATE  NOT NULL ,
        scd_end_dt            DATE  NOT NULL ,
        scd_current_ind       CHAR(1)  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
         PRIMARY KEY (acct_id)
) HASH PARTITION BY (acct_id);

CREATE TABLE DIM_AGE
(
        age_id                SMALLINT  NOT NULL ,
        age_years             SMALLINT  NOT NULL ,
        age_range_od          CHAR(15)  NOT NULL ,
        age_rand_od_order     INTEGER  NOT NULL ,
        age_range_1           CHAR(15)  NOT NULL ,
        age_range_1_order     SMALLINT  NOT NULL ,
        age_range_2           CHAR(15)  NOT NULL ,
        age_range_2_order     SMALLINT  NOT NULL ,
        age_range_3           CHAR(15)  NOT NULL ,
        age_range_3_order     SMALLINT  NOT NULL ,
        age_range_cbas        CHAR(15)  NOT NULL ,
        age_range_cbas_order  SMALLINT  NOT NULL ,
        source_system_id      SMALLINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (age_id)
) NO PARTITION;

CREATE TABLE DIM_COUNTRY_OUTRIGGER
(
        country_iso_code      CHAR(2)  NOT NULL ,
        country_iso_name      VARCHAR(100)  NOT NULL ,
        country_name          VARCHAR(30)  NOT NULL ,
        sce_region_code       CHAR(7)  NOT NULL ,
        sce_marketing_region_code  CHAR(20)  NOT NULL ,
        marketing_report_sort_order  SMALLINT  NOT NULL ,
        sce_finance_region_code  CHAR(4)  NOT NULL ,
        source_system_id      SMALLINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (country_iso_code)
) NO PARTITION;

CREATE TABLE DIM_DEMOGRAPHIC
(
        demographic_id        SMALLINT  NOT NULL ,
        country_iso_code      CHAR(2)  NOT NULL ,
        language_iso_code     CHAR(2)  NOT NULL ,
        language_name         VARCHAR(30)  NOT NULL ,
        gender                CHAR(15)  NOT NULL ,
        source_system_id      SMALLINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (demographic_id)
) NO PARTITION;

CREATE TABLE DIM_DT
(
        dt_hrchy_id           INTEGER  NOT NULL ,
        aggr_level_code       VARCHAR(30)  NOT NULL ,
        aggr_level_num        INTEGER  NOT NULL ,
        calendar_dt           DATE  NOT NULL ,
        day_of_week           SMALLINT  NOT NULL ,
        day_num_in_month      INTEGER  NOT NULL ,
        day_num_overall       INTEGER  NOT NULL ,
        day_name              VARCHAR(30)  NOT NULL ,
        day_abrv              VARCHAR(30)  NOT NULL ,
        quarter               INTEGER  NOT NULL ,
        sply_day_id           INTEGER  NOT NULL ,
        weekday_flag          CHAR(1)  NOT NULL ,
        week_id               INTEGER  NOT NULL ,
        week_num_in_year      INTEGER  NOT NULL ,
        week_num_overall      INTEGER  NOT NULL ,
        week_begin_dt         DATE  NOT NULL ,
        week_begin_dt_id      INTEGER  NOT NULL ,
        week_end_dt           DATE  NOT NULL ,
        week_end_dt_id        INTEGER  NOT NULL ,
        sply_week_id          INTEGER  NOT NULL ,
        store_day_of_week     SMALLINT  NOT NULL ,
        store_week_id         INTEGER  NOT NULL ,
        store_week_num_in_year  SMALLINT  NOT NULL ,
        store_week_num_overall  SMALLINT  NOT NULL ,
        store_week_begin_dt   DATE  NOT NULL ,
        store_week_begin_dt_id  INTEGER  NOT NULL ,
        store_week_end_dt     DATE  NOT NULL ,
        store_week_end_dt_id  INTEGER  NOT NULL ,
        month_id              INTEGER  NOT NULL ,
        month_num             INTEGER  NOT NULL ,
        month_num_overall     INTEGER  NOT NULL ,
        month_name            VARCHAR(30)  NOT NULL ,
        month_abrv            VARCHAR(30)  NOT NULL ,
        sply_month_id         INTEGER  NOT NULL ,
        year_id               INTEGER  NOT NULL ,
        year_num              INTEGER  NOT NULL ,
        year_month            VARCHAR(30)  NOT NULL ,
        last_day_in_month_ind  CHAR(1)  NOT NULL ,
        relative_day_num      INTEGER ,
        relative_week_num     INTEGER ,
        relative_month_num    INTEGER ,
        relative_year_num     INTEGER ,
        source_system_id      SMALLINT ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (dt_hrchy_id)
) NO PARTITION;

CREATE TABLE DIM_TITLE
(
        title_id              SMALLINT  NOT NULL ,
        publisher             VARCHAR(55)  NOT NULL ,
        service_provider      VARCHAR(55)  NOT NULL ,
        franchise             VARCHAR(55)  NOT NULL ,
        game                  VARCHAR(55)  NOT NULL ,
        super_title           VARCHAR(55)  NOT NULL ,
        title_name            VARCHAR(55)  NOT NULL ,
        game_genre            CHAR(20)  NOT NULL ,
        disk_title_name       VARCHAR(55)  CHARACTER SET UCS2 NOT NULL ,
        np_com_name           VARCHAR(55)  NOT NULL ,
        title_type            CHAR(15)  NOT NULL ,
        title_image_url       VARCHAR(55)  NOT NULL ,
        title_image_small_url  VARCHAR(55)  NOT NULL ,
        wws_service_provider  VARCHAR(100)  NOT NULL ,
        wws_franchise         VARCHAR(55)  NOT NULL ,
        wws_title_description  VARCHAR(100)  CHARACTER SET UCS2 NOT NULL ,
        wws_title_name        VARCHAR(55)  CHARACTER SET UCS2 NOT NULL ,
        initial_release_dt    DATE ,
        sceecat_licensor      VARCHAR(100)  NOT NULL ,
        sceecat_party_type    CHAR(15)  NOT NULL ,
        sceecat_genre         CHAR(20)  NOT NULL ,
        sceecat_publisher     VARCHAR(100)  NOT NULL ,
        sceecat_service_provider  VARCHAR(100)  NOT NULL ,
        sceecat_franchise     VARCHAR(100)  NOT NULL ,
        sceecat_game          VARCHAR(100)  NOT NULL ,
        sceecat_super_title   VARCHAR(100)  NOT NULL ,
        sceecat_title_name    VARCHAR(100)  CHARACTER SET UCS2 NOT NULL ,
        sceecat_generation_code  CHAR(15)  NOT NULL ,
        sceecat_generation_name  CHAR(20)  NOT NULL ,
        sceecat_title_release_dt  DATE ,
        pmt_title_type        VARCHAR(100)  CHARACTER SET UCS2 NOT NULL ,
        pmt_title_type_description  VARCHAR(55)  CHARACTER SET UCS2 NOT NULL ,
        pmt_title_language_code  CHAR(3)  NOT NULL ,
        pmt_service_provider  VARCHAR(55)  CHARACTER SET UCS2 NOT NULL ,
        pmt_title_name        VARCHAR(100)  CHARACTER SET UCS2 NOT NULL ,
        title_attribute_override_set  CHAR(1)  NOT NULL ,
        source_title_id       CHAR(15)  NOT NULL ,
        source_system_id      SMALLINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (title_id)
) NO PARTITION;

CREATE TABLE DIM_TENURE
(
        tenure_id             SMALLINT  NOT NULL ,
        tenure_days           INTEGER  NOT NULL ,
        tenure_weeks          INTEGER  NOT NULL ,
        tenure_weeks_range_1  CHAR(15)  NOT NULL ,
        tenure_range_1_order  INTEGER  NOT NULL ,
        tenure_weeks_range_2  CHAR(15)  NOT NULL ,
        tenure_range_2_order  INTEGER  NOT NULL ,
        tenure_weeks_range_3  CHAR(15)  NOT NULL ,
        tenure_range_3_order  INTEGER  NOT NULL ,
        tenure_weeks_range_cbas  CHAR(15)  NOT NULL ,
        tenure_range_cbas_order  INTEGER  NOT NULL ,
        source_system_id      SMALLINT  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL ,
        etl_id_updated        LARGEINT  NOT NULL ,
         PRIMARY KEY (tenure_id)
) NO PARTITION;

CREATE TABLE FCT_USER_PLAYTIME
(
        file_dt_id            INTEGER  NOT NULL ,
        title_id              SMALLINT  NOT NULL ,
        demographic_id        SMALLINT  NOT NULL ,
        acct_id               LARGEINT  NOT NULL ,
        dt_id                 INTEGER  NOT NULL ,
        age_id                SMALLINT  NOT NULL ,
        psn_tenure_id         SMALLINT  NOT NULL ,
        title_tenure_id       SMALLINT  NOT NULL ,
        total_playtime        INTEGER  NOT NULL ,
        playtime_utc_0000_0259  INTEGER  NOT NULL ,
        playtime_utc_0300_0559  INTEGER  NOT NULL ,
        playtime_utc_0600_0859  INTEGER  NOT NULL ,
        playtime_utc_0900_1159  INTEGER  NOT NULL ,
        playtime_utc_1200_1459  INTEGER  NOT NULL ,
        playtime_utc_1500_1759  INTEGER  NOT NULL ,
        playtime_utc_1800_2059  INTEGER  NOT NULL ,
        playtime_utc_2100_2359  INTEGER  NOT NULL ,
        total_playcount       INTEGER  NOT NULL ,
        last_played_dttm      TIMESTAMP(0) ,
        playtime_version      NUMERIC(1)  NOT NULL ,
        etl_id_inserted       LARGEINT  NOT NULL
)
 STORE BY (file_dt_id,title_id,demographic_id,acct_id)
 HASH PARTITION BY (acct_id);

alter table FCT_USER_PLAYTIME add constraint plt_fk1 foreign key (file_dt_id)
    references DIM_DT(DT_HRCHY_ID) NOT ENFORCED;
alter table FCT_USER_PLAYTIME add constraint plt_fk2 foreign key (title_id)
    references DIM_TITLE(TITLE_ID) NOT ENFORCED;
alter table FCT_USER_PLAYTIME add constraint plt_fk3 foreign key (demographic_id)
    references DIM_DEMOGRAPHIC(DEMOGRAPHIC_ID) NOT ENFORCED;


?section indirect_group_by
--===========================================
--------- indirect groupby section ----------
--===========================================

-- MV:
------

CREATE MATERIALIZED VIEW FCT_USER_PLAYTIME_ACCT_AGG 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        FCT_USER_PLAYTIME.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        FCT_USER_PLAYTIME.ACCT_ID               AS ACCT_ID,
        FCT_USER_PLAYTIME.TITLE_ID              AS TITLE_ID,
        MAX(FCT_USER_PLAYTIME.AGE_ID)           AS AGE_ID,
        MAX(FCT_USER_PLAYTIME.PSN_TENURE_ID)    AS PSN_TENURE_ID,
        MAX(FCT_USER_PLAYTIME.TITLE_TENURE_ID)  AS TITLE_TENURE_ID,
        SUM(FCT_USER_PLAYTIME.TOTAL_PLAYTIME)   AS TOTAL_PLAYTIME,
        COUNT(FCT_USER_PLAYTIME.TOTAL_PLAYTIME) AS COUNT_PLAYTIME,
        SUM(FCT_USER_PLAYTIME.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT,
        MAX(FCT_USER_PLAYTIME.LAST_PLAYED_DTTM) AS LAST_PLAYED_DTTM
    FROM
        FCT_USER_PLAYTIME
    GROUP BY
        FCT_USER_PLAYTIME.DEMOGRAPHIC_ID,
        FCT_USER_PLAYTIME.ACCT_ID,
        FCT_USER_PLAYTIME.TITLE_ID;

cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG';

-- Q1:
------

prepare QueryStmt from
SELECT
        F.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        D1.ACCT_ID               AS ACCT_ID,
        D2.TITLE_ID              AS TITLE_ID,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT

    FROM
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_ACCT D1 ON F.ACCT_ID = D1.ACCT_ID
        INNER JOIN DIM_TITLE D2 ON F.TITLE_ID = D2.TITLE_ID
    GROUP BY
       F.DEMOGRAPHIC_ID,
        D1.ACCT_ID,
        D2.TITLE_ID ;

execute checkPlan;

-- Q2:
------

prepare QueryStmt from
SELECT
        F.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        D1.ACCT_ID               AS ACCT_ID,
        F.TITLE_ID              AS TITLE_ID,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT

    FROM
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_ACCT D1 ON F.ACCT_ID = D1.ACCT_ID
      
    GROUP BY
       F.DEMOGRAPHIC_ID,
        D1.ACCT_ID,
        F.TITLE_ID ;

execute checkPlan;

-- Q3:
------

prepare QueryStmt from
SELECT
        F.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        F.ACCT_ID               AS ACCT_ID,
        D2.TITLE_ID              AS TITLE_ID,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT

    FROM
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D2 ON F.TITLE_ID = D2.TITLE_ID
    GROUP BY
       F.DEMOGRAPHIC_ID,
        F.ACCT_ID,
        D2.TITLE_ID ;

execute checkPlan;

-- Q4:
------

prepare QueryStmt from
SELECT
        F.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        D1.ACCT_ID               AS ACCT_ID,
        D2.TITLE_ID              AS TITLE_ID,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT

    FROM
        FCT_USER_PLAYTIME F,
       DIM_ACCT D1, 
        DIM_TITLE D2 
WHERE
F.ACCT_ID = D1.ACCT_ID
AND F.TITLE_ID = D2.TITLE_ID
    GROUP BY
       F.DEMOGRAPHIC_ID,
        D1.ACCT_ID,
        D2.TITLE_ID ;

execute checkPlan;

-- Q5:
------

prepare QueryStmt from
SELECT 
        Q1.DEMOGRAPHIC_ID,
        Q1.ACCT_ID,
        Q1.TOTAL_PLAYCOUNT
FROM 
        (SELECT F.DEMOGRAPHIC_ID, F.TOTAL_PLAYCOUNT, F.ACCT_ID
        FROM FCT_USER_PLAYTIME F
        INNER JOIN DIM_ACCT D ON F.ACCT_ID = D.ACCT_ID) Q1 ;

execute checkPlan;

-- Q6:
------

prepare QueryStmt from
SELECT
        F.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        F.ACCT_ID               AS ACCT_ID,
        F.TITLE_ID              AS TITLE_ID,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT

    FROM
        FCT_USER_PLAYTIME F
      
    GROUP BY
       F.DEMOGRAPHIC_ID,
        F.ACCT_ID,
        F.TITLE_ID ;

execute checkPlan;


?section back_joins
--===========================================
----------- back joins section --------------
--===========================================

-- MV:
------

-- need FCT_USER_PLAYTIME_ACCT_AGG already created by IGB section

CREATE MATERIALIZED VIEW SONY_TEST.FPDAGG 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.DEMOGRAPHIC_ID       AS DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TITLE_ID             AS TITLE_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TOTAL_PLAYTIME)  AS TOTAL_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.COUNT_PLAYTIME)  AS COUNT_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TOTAL_PLAYCOUNT) AS TOTAL_PLAYCOUNT,
        COUNT(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.ACCT_ID)       AS USERBASE
    FROM
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG
    GROUP BY
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TITLE_ID;


CREATE MATERIALIZED VIEW SONY_TEST.SBJT 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
SELECT FILE_DT_ID,
        TITLE_ID,
        AGE_ID,
        total_playcount
FROM FCT_USER_PLAYTIME
WHERE FILE_DT_ID = 120100301
AND AGE_ID = 20;

cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.SBJT, :CAT.SONY_TEST.FPDAGG';


-- Q1:
------

prepare QueryStmt from
SELECT  
FILE_DT_ID,
        TITLE_ID,
        AGE_ID,
        TOTAL_PLAYCOUNT
FROM    
FCT_USER_PLAYTIME 
WHERE   
FILE_DT_ID = 120100301
AND     AGE_ID = 20
AND     TITLE_ID <= 17;            


execute checkPlan;

-- Q2:
------

prepare QueryStmt from
SELECT  
        FILE_DT_ID,
        TITLE_ID,
        AGE_ID,
        TOTAL_PLAYCOUNT
FROM    
        FCT_USER_PLAYTIME 
WHERE   
        FILE_DT_ID = 120100301
AND     AGE_ID = 20
AND     TITLE_ID <= 17 
GROUP BY 
        FILE_DT_ID,
        TITLE_ID,
        AGE_ID,
        TOTAL_PLAYCOUNT; 


execute checkPlan;

-- Q3:
------

prepare QueryStmt from
SELECT  
        FILE_DT_ID,
        AGE_ID,
       SUM(TOTAL_PLAYCOUNT) AS SUM_TOTAL_PLAYTIME
FROM    
        FCT_USER_PLAYTIME 
WHERE   
        FILE_DT_ID = 120100301
AND     AGE_ID = 20
AND     TITLE_ID <= 17 
GROUP BY 
        FILE_DT_ID,
        AGE_ID;


execute checkPlan;

-- Q4:
------

prepare QueryStmt from
SELECT  
       SUM(TOTAL_PLAYCOUNT) AS SUM_TOTAL_PLAYTIME
FROM    
        FCT_USER_PLAYTIME 
WHERE   
        FILE_DT_ID = 120100301
AND     AGE_ID = 20
AND     TITLE_ID <= 17 ;



execute checkPlan;

-- Q5:
------

prepare QueryStmt from
SELECT  
        AGE_ID,
       SUM(TOTAL_PLAYCOUNT) AS SUM_TOTAL_PLAYTIME
FROM    
        FCT_USER_PLAYTIME 
GROUP BY 
        AGE_ID;


execute checkPlan;

-- Q6:
------

prepare QueryStmt from
SELECT  
        TITLE_ID,
        SUM(TOTAL_PLAYCOUNT)
FROM    
        FCT_USER_PLAYTIME 
GROUP BY 
        TITLE_ID;


execute checkPlan;

-- Q7:
------

prepare QueryStmt from
SELECT 
        D.CALENDAR_DT,
        T.TITLE_NAME,
        SUM(F.PLAYTIME_UTC_0000_0259)
FROM
        FCT_USER_PLAYTIME F 
        INNER JOIN
        DIM_DT D ON F.FILE_DT_ID = D.DT_HRCHY_ID
        INNER JOIN
        DIM_TITLE T
        ON F.TITLE_ID = T.TITLE_ID
        INNER JOIN
        DIM_DEMOGRAPHIC DE
        ON F.DEMOGRAPHIC_ID = DE.DEMOGRAPHIC_ID
        INNER JOIN 
        DIM_COUNTRY_OUTRIGGER CO
        ON DE.COUNTRY_ISO_CODE = CO.COUNTRY_ISO_CODE
WHERE
        CO.SCE_REGION_CODE = 'SCEE'
GROUP BY
         D.CALENDAR_DT,
        T.TITLE_NAME;

execute checkPlan;

?section extra_hub_tables
--===========================================
--------- extra hub tables section ----------
--===========================================

-- MV:
------

CREATE MATERIALIZED VIEW SONY_TEST.FPAGG 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID               AS ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID              AS TITLE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.AGE_ID)           AS AGE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.PSN_TENURE_ID)    AS PSN_TENURE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.TITLE_TENURE_ID)  AS TITLE_TENURE_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME)   AS TOTAL_PLAYTIME,
        COUNT(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME) AS COUNT_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.LAST_PLAYED_DTTM) AS LAST_PLAYED_DTTM
    FROM
        SONY_TEST.FCT_USER_PLAYTIME
    GROUP BY
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID;



CREATE MATERIALIZED VIEW SPT 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
SELECT  
        F.TITLE_ID AS FACT_TITLE_ID,
        F.TOTAL_PLAYCOUNT,
        D1.TITLE_ID AS DIM_TITLE_ID,
        D1.TITLE_NAME 
FROM 
        FCT_USER_PLAYTIME F INNER JOIN DIM_TITLE D1
        ON F.TITLE_ID = D1.TITLE_ID
WHERE
        F.TITLE_ID BETWEEN 0 AND 26;


cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.SPT';


-- Q1:
------

prepare QueryStmt from
SELECT
        F.TITLE_ID AS FACT_TITLE_ID,
        F.TOTAL_PLAYCOUNT,
        D.TITLE_ID AS DIM_TITLE_ID,
        D.TITLE_NAME 
        
FROM 

        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D ON F.TITLE_ID = D.TITLE_ID
WHERE
        F.TITLE_ID < 25 
        -- following predicate was added because column TITLE_ID
        -- is signed
        and F.TITLE_ID >= 0;


execute checkPlan;

-- Q2:
------

prepare QueryStmt from
SELECT  
        F.TITLE_ID AS FACT_TITLE_ID,
        F.TOTAL_PLAYCOUNT,
        D.TITLE_ID AS DIM_TITLE_ID,
        D.TITLE_NAME 
        
FROM 

        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D ON F.TITLE_ID = D.TITLE_ID
WHERE
        F.TITLE_ID < 25
        -- following predicate was added because column TITLE_ID
        -- is signed
        and F.TITLE_ID >= 0
GROUP BY 
        F.TITLE_ID,
        F.TOTAL_PLAYCOUNT,
        D.TITLE_ID,
        D.TITLE_NAME ;

execute checkPlan;

-- Q3:
------

prepare QueryStmt from
SELECT  
        F.TOTAL_PLAYCOUNT,
        D.TITLE_ID AS DIM_TITLE_ID,
        D.TITLE_NAME 
FROM 
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D ON F.TITLE_ID = D.TITLE_ID
WHERE
        F.TITLE_ID < 25
        -- following predicate was added because column TITLE_ID
        -- is signed
        and F.TITLE_ID >= 0
GROUP BY 
        F.TOTAL_PLAYCOUNT,
        D.TITLE_ID,
        D.TITLE_NAME ;


execute checkPlan;

-- Q4:
------

prepare QueryStmt from
SELECT  
        SUM(F.TOTAL_PLAYCOUNT) AS SUM_TOTAL_PLAYCOUNT,
        D.TITLE_ID AS DIM_TITLE_ID,
        D.TITLE_NAME 
FROM 
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D ON F.TITLE_ID = D.TITLE_ID
WHERE
        F.TITLE_ID < 25
        -- following predicate was added because column TITLE_ID
        -- is signed
        and F.TITLE_ID >= 0
GROUP BY 
        D.TITLE_ID,
        D.TITLE_NAME ;


execute checkPlan;

-- Q5:
------

prepare QueryStmt from
SELECT  
        SUM(F.TOTAL_PLAYCOUNT) AS SUM_TOTAL_PLAYCOUNT,

        D.TITLE_NAME 
FROM 
        FCT_USER_PLAYTIME F
        INNER JOIN DIM_TITLE D ON F.TITLE_ID = D.TITLE_ID
WHERE
        F.TITLE_ID < 25
        -- following predicate was added because column TITLE_ID
        -- is signed
        and F.TITLE_ID >= 0
GROUP BY 
        D.TITLE_NAME ;


execute checkPlan;


-- Q6:
------

prepare QueryStmt from
SELECT
        
        D1.GENDER,
        D2.TITLE_NAME,
        SUM(F.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT
    FROM
        SONY_TEST.FCT_USER_PLAYTIME F
        INNER JOIN DIM_ACCT D1 ON F.ACCT_ID = D1.ACCT_ID
        INNER JOIN DIM_TITLE D2 ON F.TITLE_ID = D2.TITLE_ID
    WHERE F.TITLE_ID < 25
          -- following predicate was added because column TITLE_ID
          -- is signed
          and F.TITLE_ID >= 0
    GROUP BY
        D1.GENDER,
        D2.TITLE_NAME ;


execute checkPlan;

?section range_predicates
--===========================================
--------- range predicates section ----------
--===========================================

-- MV:
------

CREATE MATERIALIZED VIEW SONY_TEST.SAK_DIM_DT
REFRESH ON REQUEST INITIALIZE ON CREATE
ENABLE QUERY REWRITE
AS
SELECT  
        D.RELATIVE_MONTH_NUM AS RELATIVE_MONTH_NUM,
        D.RELATIVE_WEEK_NUM  AS RELATIVE_WEEK_NUM,
        D.WEEKDAY_FLAG
    FROM
        SONY_TEST.DIM_DT D
WHERE
        D.RELATIVE_MONTH_NUM > -2 AND 
        D.RELATIVE_WEEK_NUM IN (-9,-8,-7,-6,-5,-4,-3,-2,-1);


CREATE MATERIALIZED VIEW SONY_TEST.SAK_DIM_DT_BETWEEN
REFRESH ON REQUEST INITIALIZE ON CREATE
ENABLE QUERY REWRITE
AS
    SELECT
        D.RELATIVE_MONTH_NUM AS RELATIVE_MONTH_NUM,
        D.RELATIVE_WEEK_NUM  AS RELATIVE_WEEK_NUM,
        D.WEEKDAY_FLAG       AS WEEKDAY_FLAG
    FROM
        SONY_TEST.DIM_DT D
    WHERE
        D.RELATIVE_MONTH_NUM > -2
    AND D.RELATIVE_WEEK_NUM BETWEEN -9 AND -1;

  -- The system added the following columns to the select list:
  --  D.DT_HRCHY_ID AS SYS_DT_HRCHY_ID1


cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.SAK_DIM_DT_BETWEEN';

-- Q1:
------

prepare QueryStmt from
SELECT  
RELATIVE_MONTH_NUM,
        RELATIVE_WEEK_NUM,
        WEEKDAY_FLAG

FROM 
DIM_DT

WHERE
        RELATIVE_MONTH_NUM > -1 AND 
        RELATIVE_WEEK_NUM between -7 and -3;


execute checkPlan;

-- Q2:
------


prepare QueryStmt from
SELECT  
        RELATIVE_MONTH_NUM,
        RELATIVE_WEEK_NUM,
        WEEKDAY_FLAG

FROM 
        DIM_DT

WHERE
        RELATIVE_MONTH_NUM > -1 AND 
        RELATIVE_WEEK_NUM between -7 and -3
GROUP BY
        RELATIVE_MONTH_NUM,
        RELATIVE_WEEK_NUM,
        WEEKDAY_FLAG ;


execute checkPlan;

-- Q3:
------

prepare QueryStmt from
SELECT
        RELATIVE_MONTH_NUM,
        RELATIVE_WEEK_NUM,
        WEEKDAY_FLAG

FROM 
        DIM_DT

WHERE
        RELATIVE_MONTH_NUM > -1 AND 
        RELATIVE_WEEK_NUM between -7 and -3;

execute checkPlan;

?section rollup
--===========================================
-------------- rollup section ---------------
--===========================================

CREATE MATERIALIZED VIEW SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG3
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID               AS ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID              AS TITLE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.AGE_ID)           AS AGE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.PSN_TENURE_ID)    AS PSN_TENURE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.TITLE_TENURE_ID)  AS TITLE_TENURE_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME)   AS TOTAL_PLAYTIME,
        COUNT(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME) AS COUNT_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.LAST_PLAYED_DTTM) AS LAST_PLAYED_DTTM
    FROM
        SONY_TEST.FCT_USER_PLAYTIME
    GROUP BY
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID;


cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG3';

-- Q1:
------

prepare QueryStmt from
SELECT  
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME)   AS TOTAL_PLAYTIME,
        AVG(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYCOUNT)  AS AVG_PLAYCOUNT
FROM    
        SONY_TEST.FCT_USER_PLAYTIME
GROUP BY   
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID; 

execute checkPlan;

?section user_playtime_dem_agg
--===========================================
------ user playtime dem agg section --------
--===========================================

-- MV:
------

CREATE MATERIALIZED VIEW SONY_TEST.FCT_USER_PLAYTIME_DEMOGRAPHIC_AGG2
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.DEMOGRAPHIC_ID       AS DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TITLE_ID             AS TITLE_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TOTAL_PLAYTIME)  AS TOTAL_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.COUNT_PLAYTIME)  AS COUNT_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TOTAL_PLAYCOUNT) AS TOTAL_PLAYCOUNT,
        COUNT(SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.ACCT_ID)       AS USERBASE
    FROM
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG
    GROUP BY
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG.TITLE_ID;

cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.FCT_USER_PLAYTIME_DEMOGRAPHIC_AGG2 ,:CAT.SONY_TEST.FPAGG';


-- Q1:
------

prepare QueryStmt from
SELECT
SUM(TOTAL_PLAYTIME)TOTAL_PLAYTIME
FROM FCT_USER_PLAYTIME
;


execute checkPlan;

-- Q2:
------

prepare QueryStmt from
SELECT
 TITLE_ID
,SUM(TOTAL_PLAYTIME)TOTAL_PLAYTIME
FROM FCT_USER_PLAYTIME
GROUP BY TITLE_ID   ;   


execute checkPlan;

-- Q3:
------


prepare QueryStmt from
SELECT
TITLE_NAME
,SUM(TOTAL_PLAYTIME)TOTAL_PLAYTIME
,COUNT(DISTINCT ACCT_ID) USERBASE
FROM FCT_USER_PLAYTIME F
INNER JOIN DIM_TITLE T ON T.TITLE_ID = F.TITLE_ID
GROUP BY TITLE_NAME ;     

execute checkPlan;


-- Q4:
------

prepare QueryStmt from
SELECT
 SCE_REGION_CODE
,TITLE_NAME
,SUM(TOTAL_PLAYTIME)TOTAL_PLAYTIME
FROM FCT_USER_PLAYTIME F
INNER JOIN DIM_TITLE T ON T.TITLE_ID = F.TITLE_ID
INNER JOIN DIM_DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = F.DEMOGRAPHIC_ID
INNER JOIN DIM_COUNTRY_OUTRIGGER O ON O.COUNTRY_ISO_CODE = D.COUNTRY_ISO_CODE
GROUP BY
 SCE_REGION_CODE
,TITLE_NAME

;

execute checkPlan;


?section user_playtime_acct_agg
--===========================================
------ user playtime acct agg section -------
--===========================================

-- MV:
------

CREATE MATERIALIZED VIEW SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG2 
REFRESH ON REQUEST INITIALIZE ON CREATE 
ENABLE QUERY REWRITE
AS
    SELECT
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID        AS DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID               AS ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID              AS TITLE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.AGE_ID)           AS AGE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.PSN_TENURE_ID)    AS PSN_TENURE_ID,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.TITLE_TENURE_ID)  AS TITLE_TENURE_ID,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME)   AS TOTAL_PLAYTIME,
        COUNT(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYTIME) AS COUNT_PLAYTIME,
        SUM(SONY_TEST.FCT_USER_PLAYTIME.TOTAL_PLAYCOUNT)  AS TOTAL_PLAYCOUNT,
        MAX(SONY_TEST.FCT_USER_PLAYTIME.LAST_PLAYED_DTTM) AS LAST_PLAYED_DTTM
    FROM
        SONY_TEST.FCT_USER_PLAYTIME
    GROUP BY
        SONY_TEST.FCT_USER_PLAYTIME.DEMOGRAPHIC_ID,
        SONY_TEST.FCT_USER_PLAYTIME.ACCT_ID,
        SONY_TEST.FCT_USER_PLAYTIME.TITLE_ID;

cqd mvqr_rewrite_candidates 'CAT.SONY_TEST.FCT_USER_PLAYTIME_ACCT_AGG2';





-- Q1:
------

prepare QueryStmt from
SELECT
 SUM(TOTAL_PLAYTIME)
FROM FCT_USER_PLAYTIME F
INNER JOIN DIM_TITLE T ON T.TITLE_ID = F.TITLE_ID
INNER JOIN DIM_DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = F.DEMOGRAPHIC_ID
INNER JOIN DIM_COUNTRY_OUTRIGGER O ON O.COUNTRY_ISO_CODE = D.COUNTRY_ISO_CODE
WHERE TITLE_NAME LIKE 'love sick'
GROUP BY ACCT_ID
;

execute checkPlan;


-- Q2:
------

prepare QueryStmt from
SELECT
 ACCT_ID 
FROM FCT_USER_PLAYTIME
GROUP BY ACCT_ID;


execute checkPlan;


-- Q3:
------

prepare QueryStmt from
SELECT
ACCT_ID 
FROM FCT_USER_PLAYTIME
GROUP BY 1;


execute checkPlan;


-- Q4:
------

prepare QueryStmt from
SELECT
DISTINCT  ACCT_ID 
FROM FCT_USER_PLAYTIME
GROUP BY 1;


execute checkPlan;


-- Q5:
------

prepare QueryStmt from
SELECT
 GENDER
,COUNT(DISTINCT  ACCT_ID )
FROM FCT_USER_PLAYTIME F 
INNER JOIN DIM_DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = F.DEMOGRAPHIC_ID
GROUP BY GENDER     
;

execute checkPlan;


-- Q6:
------

prepare QueryStmt from
SELECT 
        TITLE_ID,
        CASE DEMOGRAPHIC_ID 
            WHEN (128) THEN (001)
            WHEN (164) THEN (002)
            ELSE DEMOGRAPHIC_ID
        END AS CUST_DEMOGRAPHIC_ID,
        ACCT_ID,
        SUM(TOTAL_PLAYTIME) AS SUM_TOTAL_PLAYTIME
FROM 
        FCT_USER_PLAYTIME
GROUP BY
        TITLE_ID,
        DEMOGRAPHIC_ID,
        ACCT_ID
;

execute checkPlan;

-- Q7:
------

prepare QueryStmt from
SELECT
Q1.DEMOGRAPHIC_ID,
Q2.AGE_ID,
Q1.SUM_TOTAL_PLAYTIME
FROM
(
SELECT
        DEMOGRAPHIC_ID,
        TITLE_ID,
        SUM(TOTAL_PLAYTIME) AS SUM_TOTAL_PLAYTIME
FROM 
        FCT_USER_PLAYTIME
GROUP BY
        DEMOGRAPHIC_ID,
        TITLE_ID
)Q1
INNER JOIN 
(
SELECT 
        DEMOGRAPHIC_ID,
        TITLE_ID,
        AGE_ID
FROM
        FCT_USER_PLAYTIME
)Q2
ON Q1.TITLE_ID = Q2.TITLE_ID
GROUP BY 
        Q1.DEMOGRAPHIC_ID,
        Q2.AGE_ID,
        Q1.SUM_TOTAL_PLAYTIME
;

execute checkPlan;


-- Q8:
------


prepare QueryStmt from
SELECT
Q1.DEMOGRAPHIC_ID,
Q2.AGE_ID,
Q1.SUM_TOTAL_PLAYTIME
FROM
(
SELECT
        DEMOGRAPHIC_ID,
        TITLE_ID,
        SUM(TOTAL_PLAYTIME) AS SUM_TOTAL_PLAYTIME
FROM 
        FCT_USER_PLAYTIME
GROUP BY
        DEMOGRAPHIC_ID,
        TITLE_ID
)Q1
INNER JOIN 
(
SELECT 
        DEMOGRAPHIC_ID,
        TITLE_ID,
        AGE_ID
FROM
        FCT_USER_PLAYTIME
GROUP BY
        DEMOGRAPHIC_ID,
        TITLE_ID,
        AGE_ID
)Q2
ON Q1.TITLE_ID = Q2.TITLE_ID
GROUP BY 
        Q1.DEMOGRAPHIC_ID,
        Q2.AGE_ID,
        Q1.SUM_TOTAL_PLAYTIME 
;

execute checkPlan;

-- Q9:
------

prepare QueryStmt from
SELECT
Q1.DEMOGRAPHIC_ID,
Q1.TITLE_ID,
SUM(Q1.SUM_TOTAL_PLAYTIME/Q2.SUM_TOTAL_PLAYTIME)
FROM
(
SELECT
        DEMOGRAPHIC_ID,
        TITLE_ID,
        SUM(TOTAL_PLAYTIME) AS SUM_TOTAL_PLAYTIME
FROM 
        FCT_USER_PLAYTIME
GROUP BY
        DEMOGRAPHIC_ID,
        TITLE_ID
)Q1
INNER JOIN 
(
SELECT
        DEMOGRAPHIC_ID,
        TITLE_ID,
        SUM(TOTAL_PLAYTIME) AS SUM_TOTAL_PLAYTIME
FROM 
        FCT_USER_PLAYTIME
GROUP BY
        DEMOGRAPHIC_ID,
        TITLE_ID
)Q2
ON Q1.TITLE_ID = Q2.TITLE_ID
AND Q1.DEMOGRAPHIC_ID=Q2.DEMOGRAPHIC_ID

GROUP BY Q1.DEMOGRAPHIC_ID,
Q1.TITLE_ID
;

execute checkPlan;


---==========================================
---------------- END OF TEST ----------------
---==========================================
