Notes

/**
* Query Name: EU Review Log Data

**/
CREATE temporary TABLE task_state_changes distkey (task_id) sortkey (task_modified)
AS
(SELECT tsc.task_id,
convert_timezone('UTC','Asia/Calcutta',TIMESTAMP 'epoch' + tsc.task_state_change_time::BIGINT/ 1000*INTERVAL '1 second') AS task_modified,
res.editor,
tsc.task_state,
res.resolution_status,
res.resolution_status_detail,
res.resolution_note,
res.changeset,
rev.reviewer,
rev.review_status,
rev.review_note,
ROW_NUMBER() OVER (PARTITION BY tsc.task_id ORDER BY tsc.task_state_change_time) AS RANK
FROM gaiavalidation.task_state_change tsc
LEFT JOIN gaiavalidation.task_resolution_details res
ON tsc.task_id = res.task_id
AND tsc.task_state_change_time = res.task_resolution_completion_time
LEFT JOIN gaiavalidation.task_review_details rev
ON tsc.task_id = rev.task_id
AND tsc.task_state_change_time = rev.task_review_completion_time
WHERE trunc(convert_timezone('UTC','Asia/Calcutta',TIMESTAMP 'epoch' + tsc.task_state_change_time::BIGINT/ 1000*INTERVAL '1 second')) BETWEEN CURRENT_DATE -130 AND CURRENT_DATE);

CREATE temporary TABLE resolutions distkey (task_id) sortkey (task_resolved_datetime)
AS
(SELECT t1.task_id,
t1.task_modified AS task_resolved_datetime,
to_char(t1.task_modified +1,'IW') AS week,
date_trunc('month',t1.task_modified) AS month,
t1.editor,
t1.task_state,
t1.resolution_status,
t1.resolution_status_detail,
t1.resolution_note,
t1.changeset,
t1.review_status,
t1.review_note,
ROW_NUMBER() OVER (PARTITION BY t1.task_id ORDER BY t1.task_modified ASC) AS resolution_version
FROM task_state_changes t1
LEFT JOIN task_state_changes t2
ON t1.task_id = t2.task_id
AND t1.rank = t2.rank + 2
WHERE t1.task_state = 'RESOLVED'
AND t2.task_state = 'IN_PROGRESS'
AND t1.review_status IS NOT NULL
AND replace(t1.editor,'@ANT.AMAZON.COM','') NOT IN ('makkera','kotharu','ygudeti') -- excluding auditors
);

CREATE temporary TABLE error distkey (task_id)
AS
(SELECT DISTINCT err.error_report_id,
FIRST_VALUE(err.reporter) OVER (PARTITION BY tm.task_id) AS reporter,
FIRST_VALUE(err.title) OVER (PARTITION BY tm.task_id) AS title,
err.error_type,
tm.task_id
FROM gaiavalidation.error_reports err
JOIN gaiavalidation.task_error_mapping tm ON tm.error_report_id = err.error_report_id
JOIN resolutions res ON res.task_id = tm.task_id
WHERE tm.task_id IS NOT NULL
AND err.error_type NOT IN ('FB_Missing_Geometry_UK')
and err.error_type is not null);

CREATE temporary TABLE location distkey (task_id)
AS
(SELECT DISTINCT task_id,
MAX(latitude) AS lat,
MAX(longitude) AS lon
FROM gaiavalidation.task_details td
WHERE task_id IN (SELECT DISTINCT task_id FROM resolutions)
GROUP BY task_id);

CREATE temporary TABLE resolutions_final distkey (task_id) sortkeY (task_resolved_date)
AS
(SELECT DISTINCT r.task_id,
trunc(r.task_resolved_datetime) AS task_resolved_date,
r.week,
r.month,
r.editor,
r.task_state,
r.review_status,
r.resolution_version,
r.resolution_note,
r.resolution_status,
r.resolution_status_detail,
r.changeset,
r.review_note,
location.lat,
location.lon,
error.reporter,
error.title,
error.error_type,
COUNT(DISTINCT error.error_report_id) AS error_count
FROM resolutions r
JOIN error ON error.task_id = r.task_id
JOIN location ON location.task_id = r.task_id
WHERE error.reporter IS NOT NULL
GROUP BY r.task_id,
trunc(r.task_resolved_datetime),
r.week,
r.month,
r.editor,
r.task_state,
r.review_status,
r.resolution_status,
r.resolution_status_detail,
r.resolution_version,
r.resolution_note,
r.changeset,
r.review_note,
location.lat,
location.lon,
error.reporter,
error.title,
error.error_type);


SELECT count(distinct(task_id)), review_status,task_resolved_date,title
FROM resolutions_final
where task_resolved_date between Current_date-5 and current_date-1
and (title LIKE 'RNCPSSUK%'or title LIKE 'RNCPSSDE%')
and resolution_status = 'FIXED'
and reporter = 'last_mile_analytics'
group by review_status,task_resolved_date,title