--with this query we are planning to monitor the system table outside query metrics .
-- main metrics are stl query latency,query succeed count and query failure count which trigger fetch STL from S3.
--s3 data read and write volume triggered by stl query abd read file count and latency triggered by stl query.
-- and also number of clusters where padb fetch stl file from s3.

---- when we use key LIKE 'stl_' we are not only fetching stl outside cluster metrics but also stcs_xxxx tables because stcs_xxxx also fetch STL from s3.
 --so we categorize the retriving s3 logs generated from stl outside cluster or stcs as  stl_type which contains  eighter stll or stcs or invalid.we can do this by using plannode of stl_explain.
 -- The queries which have queryid -1 or 0 are considered as invalid queries.
 --For a given query in stl_explain it might have two different records with plannode like "scan on stll" or "scan on stcs",we generally consider this query as stl_type stcs.
 -- we should seprate queries that scan on stcs and queries that scan on stll

WITH stcs_queries AS (
  SELECT DISTINCT query,
         db_instance_id, 
         main_db_instance_id,
         s3_date
  FROM stl_explain
  WHERE s3_date =to_char(current_date-1,'YYYYMMDD')
  AND plannode LIKE '%stcs%'
),
stll_queries AS (
  SELECT DISTINCT query, 
         db_instance_id, 
         main_db_instance_id,
         s3_date
  FROM stl_explain
  WHERE s3_date = to_char(current_date-1,'YYYYMMDD')
  AND plannode LIKE '%stll%' 
),
-- since stll_queries also consist of query which might have another record in stl-explain with plannode like "scan on stcs",we need pure_stll_queries
--pure_stll_queries will only contain the query which "scan on stll" only.

pure_stll_queries AS (
  select * from stll_queries
  EXCEPT
  select * from stcs_queries
),
--all_queries is mainly used in order to add a new column type stl_type and define a value to it (can be stll or stcs).
--we can not add new column in pure_stll_queries because we are using EXCEPT over there and stll_queries and stcs_queries should have the same columns.

all_queries AS (
    SELECT DISTINCT s.query, 
           s.db_instance_id, 
           s.main_db_instance_id,
           s.s3_date,
           CASE
                WHEN stcs.query is not NULL then 'stcs'
            	WHEN ps.query is not NULL then 'stll'
           END
           AS stl_type 
    FROM stll_queries s 
    LEFT JOIN pure_stll_queries ps 
    ON s.query=ps.query 
    AND s.db_instance_id=ps.db_instance_id 
    AND s.main_db_instance_id = ps.main_db_instance_id 
    LEFT JOIN stcs_queries stcs 
    ON s.query=ps.query 
    AND s.db_instance_id=stcs.db_instance_id 
    AND s.main_db_instance_id = stcs.main_db_instance_id
),
--stlquery will mainly used to find the query latency and succeed and failure count.
stlquery AS(
    SELECT 	query,
    		trim(db_instance_id) AS db_instance_id, 
    		DATEDIFF('ms',starttime,endtime) as Duration, 
    		aborted,
    		s3_date
    FROM    stl_query
    WHERE
            s3_cluster_type='main'
            AND s3_date=to_char(current_date -1, 'YYYYMMDD')
            AND userid>1
    GROUP BY
            query,
            db_instance_id,
            starttime,
            endtime,
            aborted,
            s3_date
),

s3client AS(
    SELECT 	
    		DISTINCT query,
    		trim(db_instance_id) AS db_instance_id, 
    		SUM(transfer_size) AS TransferSizeSum, 
    		SUM(data_size) AS DataSizeSum,
    		SUM(DIFFERENCE(start_time, end_time)) AS s3RequestLatency,
    		http_method,
    		COUNT(DISTINCT  key) AS FileCount,
    		s3_date
    FROM
            stl_s3client
    WHERE   
            s3_cluster_type='main'
            AND s3_date=to_char(current_date -1, 'YYYYMMDD')
            AND key LIKE '%stl_%' 
    		AND bucket LIKE 'redshift-monitoring%' 
    		AND http_method LIKE 'GET%' AND strpos(key, main_db_instance_id) > 0
    		AND userid>1
    GROUP BY
            query,
            http_method,
            db_instance_id,
            s3_date
            
)
SELECT sq.query,
       sq.db_instance_id,
       sq.Duration,
       sq.aborted,
       sq.s3_date,
       sc.TransferSizeSum,
       sc.DataSizeSum,
       sc.s3RequestLatency,
       sc.FileCount,
       sc.http_method,
       CASE
            WHEN aq.stl_type IS NULL THEN 'invalid'
            ELSE aq.stl_type
       END
       AS
       stl_type
FROM stlquery sq 
JOIN s3client sc 
ON sq.db_instance_id=sc.db_instance_id 
AND sq.query=sc.query 
AND sq.s3_date=sc.s3_date
LEFT JOIN all_queries aq
ON sq.db_instance_id=aq.db_instance_id 
AND sq.query=aq.query 
AND sq.s3_date=aq.s3_date;