#1

/* line 127:52: Column 'dimension_a.id' is ambiguous */

SELECT
  CAST(
    date_add(
      'minute',
      timezone_minute("fact_table".date_dim AT TIME ZONE 'UTC'),
      date_add(
        'hour',
        timezone_hour("fact_table".date_dim AT TIME ZONE 'UTC'),
        "fact_table".date_dim
      )
    ) AS TIMESTAMP
  ) "date_dim",
  CASE
    WHEN CAST(
      date_add(
        'minute',
        timezone_minute("table_d".comparison_date AT TIME ZONE 'UTC'),
        date_add(
          'hour',
          timezone_hour("table_d".comparison_date AT TIME ZONE 'UTC'),
          "table_d".comparison_date
        )
      ) AS TIMESTAMP
    ) >= "dimension_a__subquery_date" THEN 'after'
    ELSE 'before'
  END "comparison_resul",
  COUNT(
    DISTINCT CASE
      WHEN ("fact_table".measure_value > 0) THEN "table_c".a_id
    END
  ) "measure_test_vie"
FROM
  (
    (
      SELECT
        DATE '2023-03-01' AS date_dim,
        'b1' AS b1_ref,
        'b2' AS b2_ref,
        'a1' AS direct_a_ref,
        100 AS measure_value
    )
  ) AS "fact_table"
  LEFT JOIN (
    (
      SELECT
        'b1' AS id,
        'a1' AS a_id
      UNION ALL
      SELECT
        'b2' AS id,
        'a2' AS a_id
    )
  ) AS "dimension_b_role2" ON "fact_table".b2_ref = "dimension_b_role2".id
  LEFT JOIN (
    (
      SELECT
        'b1' AS id,
        'a1' AS a_id
      UNION ALL
      SELECT
        'b2' AS id,
        'a2' AS a_id
    )
  ) AS "dimension_b_role1" ON "fact_table".b1_ref = "dimension_b_role1".id
  LEFT JOIN (
    (
      SELECT
        'a1' AS a_id,
        DATE '2023-01-01' AS date_value
      UNION ALL
      SELECT
        'a2' AS a_id,
        DATE '2023-02-01' AS date_value
    )
  ) AS "table_c" ON "fact_table".direct_a_ref = "table_c".a_id
  LEFT JOIN (
    (
      SELECT
        'a1' AS id,
        'x' AS VALUE
      UNION ALL
      SELECT
        'a2' AS id,
        'y' AS VALUE
    )
  ) AS "dimension_a" ON "dimension_b_role2".a_id = "dimension_a".id
  LEFT JOIN (
    SELECT
      "dimension_a_subquery_date_subquery__dimension_a".id "dimension_a__id",
      MIN("dimension_a_subquery_date_subquery__table_c".date_value) "dimension_a__subquery_date"
    FROM
      (
        (
          SELECT
            'a1' AS id,
            'x' AS VALUE
          UNION ALL
          SELECT
            'a2' AS id,
            'y' AS VALUE
        )
      ) AS "dimension_a_subquery_date_subquery__dimension_a"
      LEFT JOIN (
        (
          SELECT
            'a1' AS a_id,
            DATE '2023-01-01' AS date_value
          UNION ALL
          SELECT
            'a2' AS a_id,
            DATE '2023-02-01' AS date_value
        )
      ) AS "dimension_a_subquery_date_subquery__table_c" ON "dimension_a_subquery_date_subquery__dimension_a".id = "dimension_a_subquery_date_subquery__table_c".a_id
    GROUP BY
      1
  ) AS "dimension_a_subquery_date_subquery" ON "dimension_a_subquery_date_subquery"."dimension_a__id" = "dimension_a".id
  LEFT JOIN (
    (
      SELECT
        'a1' AS id,
        'x' AS VALUE
      UNION ALL
      SELECT
        'a2' AS id,
        'y' AS VALUE
    )
  ) AS "dimension_a" ON "dimension_b_role1".a_id = "dimension_a".id
  LEFT JOIN (
    SELECT
      "dimension_a_subquery_date_subquery__dimension_a".id "dimension_a__id",
      MIN("dimension_a_subquery_date_subquery__table_c".date_value) "dimension_a__subquery_date"
    FROM
      (
        (
          SELECT
            'a1' AS id,
            'x' AS VALUE
          UNION ALL
          SELECT
            'a2' AS id,
            'y' AS VALUE
        )
      ) AS "dimension_a_subquery_date_subquery__dimension_a"
      LEFT JOIN (
        (
          SELECT
            'a1' AS a_id,
            DATE '2023-01-01' AS date_value
          UNION ALL
          SELECT
            'a2' AS a_id,
            DATE '2023-02-01' AS date_value
        )
      ) AS "dimension_a_subquery_date_subquery__table_c" ON "dimension_a_subquery_date_subquery__dimension_a".id = "dimension_a_subquery_date_subquery__table_c".a_id
    GROUP BY
      1
  ) AS "dimension_a_subquery_date_subquery" ON "dimension_a_subquery_date_subquery"."dimension_a__id" = "dimension_a".id
  LEFT JOIN (
    (
      SELECT
        'a1' AS a_id,
        DATE '2023-01-15' AS comparison_date
      UNION ALL
      SELECT
        'a2' AS a_id,
        DATE '2023-02-15' AS comparison_date
    )
  ) AS "table_d" ON "dimension_a".id = "table_d".a_id
GROUP BY
  1,
  2