planbug.sql

application/octet-stream

Download

DROP TABLE f_opportunitysnapshot;
DROP TABLE f_opportunity;
DROP TABLE dwh_dm_aab5o6l7ovaege0;
DROP TABLE dwh_dm_aabzo6l7ovaege0;
DROP TABLE dwh_dm_aab5o6l7o9aegc4;

CREATE TABLE f_opportunitysnapshot(
  dt_snapshotdate int,
  f_snapshotid integer,
  dt_createddate integer,
  dt_createddate_id integer,
  opportunityid_id integer,
  dt_closedate_id integer,
  f_amount integer,

  dt_closedate integer
);

CREATE TABLE f_opportunity(
  id integer,
  isclosed_id integer,
  status_id integer
);

CREATE TABLE dwh_dm_aab5o6l7ovaege0(
  id integer,
  id_quarter integer
);

CREATE TABLE dwh_dm_aabzo6l7ovaege0(
  id integer
);

CREATE TABLE dwh_dm_aab5o6l7o9aegc4(
  id integer,
  id_year integer,
  id_quarter integer,
  id_quarter_in_year integer
);

CREATE TEMPORARY TABLE qt_bec53689165ec7a8505664c94fb6eb8c AS WITH
 "subQuery_0" AS ( SELECT CAST( MAX( "f_opportunitysnapshot"."f_snapshotid" ) AS double precision ) AS "m_1" FROM "f_opportunitysnapshot" WHERE ( ( "f_opportunitysnapshot"."dt_snapshotdate" <= 40588 ) ) GROUP BY NULL::int ),
 "subQuery_12" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id_quarter" AS "a_440", "f_opportunity"."id" AS "a_714", CAST( SUM( ( CASE WHEN ( ( "f_opportunitysnapshot"."dt_closedate" - "f_opportunitysnapshot"."dt_createddate" ) IS NULL ) THEN ( "f_opportunitysnapshot"."dt_closedate" - "f_opportunitysnapshot"."dt_createddate" ) ELSE 1 END ) ) AS double precision ) AS "m_1" FROM "subQuery_0", "f_opportunity", "dwh_dm_aab5o6l7ovaege0", "dwh_dm_aabzo6l7ovaege0", "f_opportunitysnapshot" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" = "f_opportunitysnapshot"."dt_closedate_id" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "f_opportunity"."isclosed_id" = 61 ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) AND ( "f_opportunity"."status_id" = 5214 ) ) GROUP BY 1, 2 ),
 "subQuery_9" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id" AS "a_374", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "dwh_dm_aab5o6l7ovaege0" WHERE ( ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = 8045 ) ) GROUP BY 1 ),
 "subQuery_10" AS ( SELECT CAST( SUM( "subQuery_9"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_9" GROUP BY NULL::int ),
 "subQuery_13" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id" AS "a_374", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "dwh_dm_aabzo6l7ovaege0", "dwh_dm_aab5o6l7ovaege0" WHERE ( ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) ) GROUP BY 1 ),
 "subQuery_1" AS ( SELECT "dwh_dm_aab5o6l7o9aegc4"."id_year" AS "a_339", "f_opportunitysnapshot"."opportunityid_id" AS "a_714", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "subQuery_0", "dwh_dm_aab5o6l7o9aegc4", "f_opportunitysnapshot" WHERE ( ( "dwh_dm_aab5o6l7o9aegc4"."id" = "f_opportunitysnapshot"."dt_createddate_id" ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aab5o6l7o9aegc4"."id_quarter" < 8045 ) AND ( "dwh_dm_aab5o6l7o9aegc4"."id_quarter_in_year" = 1 ) ) GROUP BY 1, 2 ),
 "subQuery_2" AS ( SELECT "subQuery_1"."a_339" AS "a_339", CAST( SUM( "subQuery_1"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_1" GROUP BY 1 ),
 "subQuery_4" AS ( SELECT CAST( SUM( ( CASE WHEN ( ( 0.85 * "subQuery_2"."m_1" ) IS NULL ) THEN ( 0.85 * "subQuery_2"."m_1" ) ELSE 1 END ) ) AS double precision ) AS "m_1" FROM "subQuery_2" GROUP BY NULL::int ),
 "subQuery_5" AS ( SELECT CAST( SUM( "f_opportunitysnapshot"."f_amount" ) AS double precision ) AS "m_1" FROM "subQuery_0", "dwh_dm_aab5o6l7ovaege0", "f_opportunity", "f_opportunitysnapshot" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" = "f_opportunitysnapshot"."dt_closedate_id" ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" < 8045 ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" > ( 8045 - 5 ) ) AND ( "f_opportunity"."status_id" = 5214 ) ) GROUP BY NULL::int ),
 "subQuery_14" AS ( SELECT CAST( SUM( "subQuery_13"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_13" GROUP BY NULL::int ),
 "subQuery_15" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id" AS "a_374", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "dwh_dm_aabzo6l7ovaege0", "dwh_dm_aab5o6l7ovaege0" WHERE ( ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" < 40588 ) ) GROUP BY 1 ),
 "subQuery_16" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id_quarter" AS "a_440", CAST( SUM( "subQuery_15"."m_1" ) AS double precision ) AS "m_1" FROM "dwh_dm_aab5o6l7ovaege0", "subQuery_15" WHERE ( ( "dwh_dm_aab5o6l7ovaege0"."id" = "subQuery_15"."a_374" ) ) GROUP BY 1 ),
 "subQuery_11" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id_quarter" AS "a_440", "f_opportunity"."id" AS "a_714", CAST( SUM( ( "f_opportunitysnapshot"."dt_closedate" - "f_opportunitysnapshot"."dt_createddate" ) ) AS double precision ) AS "m_1" FROM "subQuery_0", "f_opportunity", "dwh_dm_aab5o6l7ovaege0", "dwh_dm_aabzo6l7ovaege0", "f_opportunitysnapshot" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" = "f_opportunitysnapshot"."dt_closedate_id" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "f_opportunity"."isclosed_id" = 61 ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) AND ( "f_opportunity"."status_id" = 5214 ) ) GROUP BY 1, 2 ),
 "subQuery_17" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id_quarter" AS "a_440", "f_opportunity"."id" AS "a_714", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "subQuery_14" INNER JOIN "subQuery_0" ON TRUE, "f_opportunity", "dwh_dm_aab5o6l7ovaege0", "f_opportunitysnapshot", "dwh_dm_aabzo6l7ovaege0", "subQuery_12", "subQuery_11", "subQuery_16" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "f_opportunitysnapshot"."opportunityid_id" = "subQuery_12"."a_714" ) AND ( "subQuery_12"."a_714" = "subQuery_11"."a_714" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = "subQuery_12"."a_440" ) AND ( "subQuery_12"."a_440" = "subQuery_11"."a_440" ) AND ( "subQuery_11"."a_440" = "subQuery_16"."a_440" ) AND ( "subQuery_16"."a_440" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" = "f_opportunitysnapshot"."dt_closedate_id" ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) AND ( "f_opportunity"."status_id" = 5214 ) AND ( ( CASE WHEN ( "subQuery_12"."m_1" = 0 ) THEN NULL ELSE ( "subQuery_11"."m_1" / "subQuery_12"."m_1" ) END ) < ( "subQuery_14"."m_1" - "subQuery_16"."m_1" ) ) ) GROUP BY 1, 2 ),
 "subQuery_18" AS ( SELECT "subQuery_17"."a_440" AS "a_440", CAST( SUM( "subQuery_17"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_17" GROUP BY 1 ),
 "subQuery_19" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id_quarter" AS "a_440", "f_opportunity"."id" AS "a_714", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "subQuery_0", "f_opportunity", "f_opportunitysnapshot", "dwh_dm_aab5o6l7ovaege0", "dwh_dm_aabzo6l7ovaege0" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "f_opportunitysnapshot"."dt_closedate_id" = "dwh_dm_aab5o6l7ovaege0"."id" ) AND ( "f_opportunity"."isclosed_id" = 61 ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) ) GROUP BY 1, 2 ),
 "subQuery_20" AS ( SELECT "subQuery_19"."a_440" AS "a_440", CAST( SUM( "subQuery_19"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_19" GROUP BY 1 ),
 "subQuery_21" AS ( SELECT CAST( SUM( ( CASE WHEN ( ( 0.85 * "subQuery_20"."m_1" ) = 0 ) THEN NULL ELSE ( ( 0.85 * "subQuery_18"."m_1" ) / ( 0.85 * "subQuery_20"."m_1" ) ) END ) ) AS double precision ) AS "m_1" FROM "dwh_dm_aabzo6l7ovaege0", "subQuery_18", "subQuery_20" WHERE ( ( "subQuery_18"."a_440" = "subQuery_20"."a_440" ) AND ( "subQuery_20"."a_440" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) ) GROUP BY NULL::int ),
 "subQuery_6" AS ( SELECT CAST( SUM( ( CASE WHEN ( "f_opportunitysnapshot"."f_amount" IS NULL ) THEN "f_opportunitysnapshot"."f_amount" ELSE 1 END ) ) AS double precision ) AS "m_1" FROM "subQuery_0", "dwh_dm_aab5o6l7ovaege0", "f_opportunity", "f_opportunitysnapshot" WHERE ( ( "f_opportunity"."id" = "f_opportunitysnapshot"."opportunityid_id" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" = "f_opportunitysnapshot"."dt_closedate_id" ) AND ( "f_opportunitysnapshot"."f_snapshotid" = "subQuery_0"."m_1" ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" < 8045 ) AND ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" > ( 8045 - 5 ) ) AND ( "f_opportunity"."status_id" = 5214 ) ) GROUP BY NULL::int ),
 "subQuery_22" AS ( SELECT CAST( SUM( ( CASE WHEN ( ( CASE WHEN ( ( 0.85 * "subQuery_20"."m_1" ) = 0 ) THEN NULL ELSE ( ( 0.85 * "subQuery_18"."m_1" ) / ( 0.85 * "subQuery_20"."m_1" ) ) END ) IS NULL ) THEN ( CASE WHEN ( ( 0.85 * "subQuery_20"."m_1" ) = 0 ) THEN NULL ELSE ( ( 0.85 * "subQuery_18"."m_1" ) / ( 0.85 * "subQuery_20"."m_1" ) ) END ) ELSE 1 END ) ) AS double precision ) AS "m_1" FROM "dwh_dm_aabzo6l7ovaege0", "subQuery_18", "subQuery_20" WHERE ( ( "subQuery_18"."a_440" = "subQuery_20"."a_440" ) AND ( "subQuery_20"."a_440" = ( "dwh_dm_aabzo6l7ovaege0"."id" - 1 ) ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" < 8045 ) AND ( "dwh_dm_aabzo6l7ovaege0"."id" > ( 8045 - 5 ) ) ) GROUP BY NULL::int ),
 "subQuery_7" AS ( SELECT "dwh_dm_aab5o6l7ovaege0"."id" AS "a_374", CAST( MIN( 1 ) AS double precision ) AS "m_1" FROM "dwh_dm_aab5o6l7ovaege0" WHERE ( ( "dwh_dm_aab5o6l7ovaege0"."id_quarter" = 8045 ) AND ( "dwh_dm_aab5o6l7ovaege0"."id" < 40588 ) ) GROUP BY 1 ),
 "subQuery_8" AS ( SELECT CAST( SUM( "subQuery_7"."m_1" ) AS double precision ) AS "m_1" FROM "subQuery_7" GROUP BY NULL::int ),
 "subQuery_3" AS ( SELECT CAST( SUM( ( 0.85 * "subQuery_2"."m_1" ) ) AS double precision ) AS "m_1" FROM "subQuery_2" GROUP BY NULL::int ),
 "subQuery_23" AS ( SELECT CAST( ( ( ( ( CASE WHEN ( "subQuery_4"."m_1" = 0 ) THEN NULL ELSE ( "subQuery_3"."m_1" / "subQuery_4"."m_1" ) END ) * ( CASE WHEN ( "subQuery_6"."m_1" = 0 ) THEN NULL ELSE ( "subQuery_5"."m_1" / "subQuery_6"."m_1" ) END ) ) * ( 1 - ( CASE WHEN ( "subQuery_10"."m_1" = 0 ) THEN NULL ELSE ( "subQuery_8"."m_1" / "subQuery_10"."m_1" ) END ) ) ) * ( CASE WHEN ( "subQuery_22"."m_1" = 0 ) THEN NULL ELSE ( "subQuery_21"."m_1" / "subQuery_22"."m_1" ) END ) ) AS double precision ) AS "m_1" FROM "subQuery_6" INNER JOIN "subQuery_5" ON TRUE INNER JOIN "subQuery_22" ON TRUE INNER JOIN "subQuery_4" ON TRUE INNER JOIN "subQuery_21" ON TRUE INNER JOIN "subQuery_3" ON TRUE INNER JOIN "subQuery_10" ON TRUE INNER JOIN "subQuery_8" ON TRUE )
( SELECT CAST( "subQuery_23"."m_1" AS double precision ) AS "m_3377" FROM "subQuery_23" );