# View: dt_store_availability_shazam

**View Name:** dt_store_availability_shazam
**Table Source:** `Derived from: All_Available, Prod.Dim_Day, Prod.Dim_Store, Prod.Shazam_Daily_Events, Prod.Store_Slas + 3 more`
**File Path:** `dt_store_availability_shazam.view.lkml`

## Overview

- **File Size:** 5897 bytes
- **Lines of Code:** 192
- **Dimensions:** 5
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- EC7063; font-size:100%;">{{ rendered_value }}</p>
- 58FA58; font-size:100%;">{{ rendered_value }}</p>

## Dimensions

| Name | Type |
|------|------|
| `storeid` | number |
| `storename` | string |
| `error_dates` | string |
| `max_complete_date` | date |
| `overal_status` | string |

## Derived Table

```sql
sql:
    WITH STORE_DATA_TOTAL AS (
      SELECT
        8888 AS STOREID,
        DOWNLOAD_DATE AS DOWNLOAD_ACTIVITY_DATE,
        COUNT(DISTINCT EVENT_ID) AS UNITS
      FROM FACTS.PROD.SHAZAM_DAILY_EVENTS
      WHERE DOWNLOAD_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE())
        AND CURRENT_DATE()
      GROUP BY 1, 2
    ),

    ALL_AVAILABLE AS (
      SELECT
        S.STOREID,
        DD.DISPLAYDATE AS DOWNLOAD_ACTIVITY_DATE
      FROM FACTS.PROD.DIM_DAY DD
      CROSS JOIN (SELECT DISTINCT STOREID FROM STORE_DATA_TOTAL) S
      WHERE DD.DISPLAYDATE > DATEADD('WEEK', -12, CURRENT_DATE())
        AND DD.DISPLAYDATE <= CURRENT_DATE()
    ),

    STORE_DATA_SUMMARY AS (
      SELECT
        AA.STOREID,
        AA.DOWNLOAD_ACTIVITY_DATE,
        SDS.UNITS
      FROM ALL_AVAILABLE AA
      LEFT JOIN STORE_DATA_TOTAL SDS
        ON AA.STOREID = SDS.STOREID
          AND AA.DOWNLOAD_ACTIVITY_DATE = SDS.DOWNLOAD_ACTIVITY_DATE
      ORDER BY 1, 2
    ),

    STORE_THRESHOLDS AS (
      SELECT
        STOREID,
        PERCENTILE_CONT(.25) WITHIN GROUP (ORDER BY UNITS) AS Q1,
        PERCENTILE_CONT(.75) WITHIN GROUP (ORDER BY UNITS) AS Q3,
        Q3 - Q1 AS IQR,
        Q1 - 4*IQR AS MIN_THRESHOLD,
        Q3 + 8*IQR AS MAX_THRESHOLD
      FROM STORE_DATA_SUMMARY
      WHERE DOWNLOAD_ACTIVITY_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE())
        AND DATEADD('WEEK', -4, CURRENT_DATE())
      GROUP BY STOREID
    ),

    EXPANDED_STORE_DATA_SUMMARY AS (
      SELECT
        SDS.STOREID,
        SDS.DOWNLOAD_ACTIVITY_DATE,
        SDS.UNITS,
        ROUND(IFF(ST.MIN_THRESHOLD < 0, 0, ST.MIN_THRESHOLD), 0) AS MIN_THRESHOLD,
        ROUND(ST.MAX_THRESHOLD, 0) AS MAX_THRESHOLD,
        IFF(SDS.UNITS IS NULL, 1, NULL) as MISSING_DATE,
        IFF(SDS.UNITS < ST.MIN_THRESHOLD, 1, NULL) AS INCOMPLETE_DATE,
        IFF(SDS.UNITS > ST.MAX_THRESHOLD, 1, NULL) AS DUPLICATED_DATE
      FROM STORE_DATA_SUMMARY SDS
      LEFT JOIN STORE_THRESHOLDS ST
        ON SDS.STOREID = ST.STOREID
    ),

    MAX_AVAILABLE_DATES AS (
      SELECT
        STOREID,
        MAX(DOWNLOAD_ACTIVITY_DATE) AS STORE_HIGH_WATERMARK
      FROM EXPANDED_STORE_DATA_SUMMARY
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NULL
      GROUP BY 1
    ),

    SUMMARY_SHAZAM_STORE_AVAILABILITY AS (
      SELECT
        ESDS.STOREID,
        ESDS.DOWNLOAD_ACTIVITY_DATE,
        ESDS.UNITS,
        ESDS.MIN_THRESHOLD,
        ESDS.MAX_THRESHOLD,
        ESDS.MISSING_DATE,
        ESDS.INCOMPLETE_DATE,
        ESDS.DUPLICATED_DATE,
        MAD.STORE_HIGH_WATERMARK
      FROM EXPANDED_STORE_DATA_SUMMARY ESDS
      LEFT JOIN MAX_AVAILABLE_DATES MAD
        ON ESDS.STOREID = MAD.STOREID
      ORDER BY
        STOREID, DOWNLOAD_ACTIVITY_DATE
    ),

    LOOKER_STORE_SUMMARY AS (
      SELECT
        AVAIL.STOREID,
        CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD, CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS MISSING_DATE,
        CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD, CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS INCOMPLETE_DATE,
        CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD, CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS DUPLICATED_DATE,
        STORE_HIGH_WATERMARK
      FROM SUMMARY_SHAZAM_STORE_AVAILABILITY AVAIL
      INNER JOIN INTELLIGENCE.PROD.STORE_SLAS SLAS ON AVAIL.STOREID = SLAS.STOREID
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      GROUP BY 1, 2, 3, 4, 5
    ),

    STORES_AGGREGATED AS (
      SELECT
        STOREID,
        LISTAGG(RIGHT(MISSING_DATE, 5), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
        LISTAGG(RIGHT(INCOMPLETE_DATE, 5), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
        LISTAGG(RIGHT(DUPLICATED_DATE, 5), ', ') WITHIN GROUP (ORDER BY DUPLICATED_DATE) AS DUPLICATED_DATES,
        STORE_HIGH_WATERMARK
      FROM LOOKER_STORE_SUMMARY
      GROUP BY 1, 5
    )

    SELECT
      SA.STOREID,
      CASE WHEN SA.STOREID = 8888
        THEN 'Shazam'
        ELSE DS.STORENAME
        END AS STORENAME,
      '\n'||
      CASE WHEN MISSING_DATES != ''
        THEN '-Missing Dates: ' || MISSING_DATES || '\n'
        ELSE '' END ||
      CASE WHEN INCOMPLETE_DATES  != ''
        THEN '-Incomplete Dates: ' || INCOMPLETE_DATES || '\n'
        ELSE '' END ||
      CASE WHEN DUPLICATED_DATES != ''
        THEN '-Duplicated Dates: ' || DUPLICATED_DATES || '\n'
        ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE
    FROM STORES_AGGREGATED SA
    LEFT JOIN FACTS.PROD.DIM_STORE DS ON SA.STOREID = DS.STOREID
    ORDER BY 1
;;
```