connect to ****** user vvm using ********;

UPDATE COMMAND OPTIONS USING C OFF;

DROP TABLE phone_codes;
DROP TABLE excl_phones;
DROP TABLE calls;

CREATE TABLE phone_codes(
  townCode INTEGER NOT NULL,
  townName VARCHAR( 20 ) NOT NULL
) NOT LOGGED INITIALLY;

INSERT INTO phone_codes
WITH 
  temp(x,y) AS (
    VALUES( 0 , 'Town #0')
    UNION ALL
    SELECT x+ 1 , 'Town #'||CHAR(x+ 1 )
    FROM temp
    WHERE x< 999 
  )
SELECT x,y
FROM temp;
  
CREATE UNIQUE INDEX phonecodes1 ON phone_codes(townCode) ALLOW REVERSE SCANS;
CREATE UNIQUE INDEX phonecodes2 ON phone_codes(townCode, townName) ALLOW REVERSE SCANS;
ALTER TABLE phone_codes ADD CONSTRAINT pk_pc PRIMARY KEY(townCode);
RUNSTATS ON TABLE vvm.phone_codes WITH DISTRIBUTION AND DETAILED INDEXES ALL;
COMMIT;
 --SELECT * FROM phone_codes;
 

CREATE TABLE excl_phones(
  phone BIGINT NOT NULL
) NOT LOGGED INITIALLY;

INSERT INTO excl_phones
WITH 
  temp(x) AS (
    VALUES( 0 )
    UNION ALL
    SELECT x+ 1 
    FROM temp
    WHERE x < 1000 
  )
SELECT x+ 1234500000 
FROM temp;
CREATE UNIQUE INDEX exclphones1 ON excl_phones(phone) ALLOW REVERSE SCANS;
RUNSTATS ON TABLE vvm.phone_codes WITH DISTRIBUTION AND DETAILED INDEXES ALL;
COMMIT;
 --SELECT * FROM excl_phones;
 

CREATE TABLE calls(
  phone    BIGINT  NOT NULL,
  duration INTEGER NOT NULL,
  townCode INTEGER NOT NULL
)  NOT LOGGED INITIALLY;

 -- T1
 
VALUES(current timestamp);

INSERT INTO calls
WITH 
  temp(n, phone, code, duration) AS (
    VALUES( 1 , RAND( 1 )* 1000000 , RAND()* 100 , RAND()* 1000  )
    UNION ALL
    SELECT n+ 1 , RAND()* 1000000 , RAND()* 100 , RAND()* 1000  
    FROM temp
    WHERE n< 20000000 
  )
SELECT phone+code* 10000000 , duration, code
FROM temp;
 -- T2
 
VALUES(current timestamp);
CREATE INDEX calls1 ON calls(townCode, phone, duration) ALLOW REVERSE SCANS;
RUNSTATS ON TABLE vvm.calls WITH DISTRIBUTION AND DETAILED INDEXES ALL;

 -- T3
 
VALUES(current timestamp);

COMMIT;
SELECT 
   char(cast(phone as decimal( 10 , 0 ))) phone, 
   char(cast(townCode as decimal( 3 , 0 ))) townCode, 
   duration 
FROM calls
FETCH FIRST  100  ROWS ONLY;

 -- T4
 
VALUES(current timestamp);

SELECT T2.townCode, T2.townName, SUM(T1.duration) AS SUM
FROM
  (SELECT tt1.townCode, tt1.duration
   FROM calls tt1
   WHERE tt1.duration >  10 
     AND NOT tt1.phone IN (SELECT tt2.phone FROM excl_phones tt2)
  ) T1
  RIGHT JOIN phone_codes T2 ON T1.townCode = T2.townCode
GROUP BY T2.townCode, T2.townName;
  
 -- T5
 
VALUES(current timestamp);

SELECT T2.townCode, T2.townName, SUM(T1.duration) AS SUM
FROM
  (SELECT tt1.townCode, tt1.duration
   FROM calls tt1
   WHERE tt1.duration >  10 
     AND NOT EXISTS (SELECT * FROM excl_phones tt2 where tt1.phone=tt2.phone)
  ) T1
  RIGHT JOIN phone_codes T2 ON T1.townCode = T2.townCode
GROUP BY T2.townCode, T2.townName;

 -- T6
 
VALUES(current timestamp);

SELECT T2.townCode, T2.townName, T1.duration_sum AS SUM
FROM
  (SELECT tt1.townCode, SUM(tt1.duration) duration_sum
   FROM calls tt1
   WHERE tt1.duration >  10 
     AND NOT tt1.phone IN (SELECT tt2.phone FROM excl_phones tt2)
   GROUP BY tt1.townCode
  ) T1
  RIGHT JOIN phone_codes T2 ON T1.townCode = T2.townCode
;
  
 -- T7
 
VALUES(current timestamp);

SELECT T2.townCode, T2.townName, T1.duration_sum AS SUM
FROM
  (SELECT tt1.townCode, SUM(tt1.duration) duration_sum
   FROM calls tt1
   WHERE tt1.duration >  10 
     AND NOT EXISTS (SELECT * FROM excl_phones tt2 where tt1.phone=tt2.phone)
   GROUP BY tt1.townCode
  ) T1
  RIGHT JOIN phone_codes T2 ON T1.townCode = T2.townCode
;

 -- T8
 
VALUES(current timestamp);

 -- TERMINATE;