view:  soundcloud_statistics
{
  derived_table: {
    sql: WITH DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY AS (SELECT
        TO_CHAR(TO_DATE(D.DISPLAYDATE), 'YYYY-MM-DD') AS ENGAGEMENT_DATE,
        STATISTICS.PLAYS_ENGAGEMENT AS PLAYS,
        STATISTICS.ARTIST
      FROM DEV_ENGINEERING.NPALCHINSKIY.STAGING_RAW_SOUNDCLOUD_STATISTICS_106 AS STATISTICS
      LEFT JOIN DEV_ENGINEERING.NPALCHINSKIY.DIM_DAY D
      ON D.DISPLAYDATE = STATISTICS.LAST_PROCESSING_DATE
      ORDER BY 1)
      SELECT
          DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.ARTIST AS "ARTIST",
          DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.ENGAGEMENT_DATE AS "ENGAGEMENT_DATE",
          COALESCE(DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.PLAYS, 0) AS "PLAYS"
      FROM DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY
      WHERE (DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.ENGAGEMENT_DATE) >= (DATEADD('day', -29, CURRENT_DATE()))
      GROUP BY
          DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.ARTIST, DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.ENGAGEMENT_DATE, DT_SOUNDCLOUD_ENGAGEMENT_BY_DAY.PLAYS
      ORDER BY
          1 DESC;;
  }

  dimension: date {
    type: date
    sql: ${TABLE}.ENGAGEMENT_DATE;;
  }

  dimension: artist {
    type: string
    sql: ${TABLE}.ARTIST;;
  }

  measure: engagement {
    type: sum
    sql:  ${TABLE}.PLAYS;;
    value_format: "0.00"
  }
}