# View: dt_store_availability_analytics_sme

**View Name:** dt_store_availability_analytics_sme_updated
**Table Source:** `Derived from: Prod.Data_Availability_By_Store_Daily, Prod.Dim_Day, Prod.Dim_Feed, Prod.Dim_Licensor, Prod.Fact_Youtube_Asset_Analytics + 3 more`
**File Path:** `dt_store_availability_analytics_sme.view.lkml`

## Overview

- **File Size:** 12432 bytes
- **Lines of Code:** 350
- **Dimensions:** 7
- **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 |
|------|------|
| `feedid` | number |
| `feedname` | string |
| `error_dates` | string |
| `max_complete_date` | date |
| `overall_status` | string |
| `priority_ranking` | string |
| `priority_ranking_sort` | string |

## Derived Table

```sql
sql:
          with table1 as (

WITH dt_store_availability_analytics AS (WITH STORE_SUMMARY AS (
      SELECT
        AVAIL.FEEDID,
        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 FACTS.PROD.DATA_AVAILABILITY_BY_STORE_DAILY AVAIL
      INNER JOIN INTELLIGENCE.PROD.STORE_SLAS SLAS ON AVAIL.FEEDID = SLAS.FEEDID
      INNER JOIN FACTS.PROD.DIM_LICENSOR LICENSOR ON LICENSOR.FEEDID = SLAS.FEEDID
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      AND LICENSOR.DISTRIBUTOR = 'sme'
      GROUP BY 1, 2, 3, 4, 5
    ),
    FILTERED_SUMMARY AS (
      SELECT *
        FROM STORE_SUMMARY
        WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),
    STORES_AGGREGATED AS (
      SELECT
        FEEDID,
        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 FILTERED_SUMMARY
      GROUP BY 1, 5
    )
    SELECT
      SA.FEEDID,
      DF.FEEDNAME,
      '\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_FEED DF ON SA.FEEDID = DF.FEEDID
    ORDER BY 1
)
SELECT
    dt_store_availability_analytics.FEEDID  AS FEEDID,
    dt_store_availability_analytics.FEEDNAME  AS FEEDNAME,
    CASE
WHEN length(dt_store_availability_analytics.ERROR_DATES)<3 THEN '0'
ELSE '1'
END AS OVERALL_STATUS,
    CASE
WHEN length(dt_store_availability_analytics.ERROR_DATES)<3 THEN 'Good'
ELSE 'Error'
END AS OVERALLSTATUS,
    dt_store_availability_analytics.ERROR_DATES  AS ERRORDATES,
        (TO_CHAR(TO_DATE(dt_store_availability_analytics.MAX_COMPLETE_DATE ), 'YYYY-MM-DD')) AS MAXOMPLETEDDATE,
    CASE
WHEN dt_store_availability_analytics.feedid=1 THEN '00'
WHEN dt_store_availability_analytics.feedid=4 THEN '01'
WHEN dt_store_availability_analytics.feedid=13 THEN '02'
WHEN dt_store_availability_analytics.feedid=16 THEN '05'
WHEN dt_store_availability_analytics.feedid=9 THEN '06'
WHEN dt_store_availability_analytics.feedid=3 THEN '07'
WHEN dt_store_availability_analytics.feedid=11 THEN '10'
ELSE '12'
END AS PRIORITYRANKINGSORT,
    CASE
WHEN dt_store_availability_analytics.feedid=1 THEN '1'
WHEN dt_store_availability_analytics.feedid=4 THEN '2'
WHEN dt_store_availability_analytics.feedid=13 THEN '3'
WHEN dt_store_availability_analytics.feedid=16 THEN '6'
WHEN dt_store_availability_analytics.feedid=9 THEN '7'
WHEN dt_store_availability_analytics.feedid=3 THEN '8'
WHEN dt_store_availability_analytics.feedid=14 THEN '9'
WHEN dt_store_availability_analytics.feedid=11 THEN '12'
ELSE ''
END AS PRIORITYRANKING
FROM dt_store_availability_analytics
WHERE (dt_store_availability_analytics.FEEDID != 38)
GROUP BY
    (TO_DATE(dt_store_availability_analytics.MAX_COMPLETE_DATE )),
    1,
    2,
    3,
    4,
    5,
    7,
    8
ORDER BY
    2
FETCH NEXT 500 ROWS ONLY),



table2 as (


WITH dt_store_availability_analytics_sme AS (
  WITH dt_store_availability_analytics AS (
    WITH STORE_SUMMARY AS (
      SELECT
        AVAIL.FEEDID,
        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 (
      WITH STORE_DATA_TOTAL AS (
        SELECT
          FEEDID,
          DOWNLOAD_ACTIVITY_DATE,
          SUM(VIEWS) AS UNITS
        FROM facts.prod.fact_youtube_video_analytics
        WHERE DOWNLOAD_ACTIVITY_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
          AND (LICENSORID IN (SELECT LICENSORID FROM facts.prod.dim_licensor WHERE DISTRIBUTOR = 'sme'))
        GROUP BY 1, 2
        UNION ALL
        SELECT
          FEEDID,
          DOWNLOAD_ACTIVITY_DATE,
          SUM(VIEWS) AS UNITS
        FROM facts.prod.fact_youtube_asset_analytics
        WHERE DOWNLOAD_ACTIVITY_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
          AND (LICENSORID IN (SELECT LICENSORID FROM facts.prod.dim_licensor WHERE DISTRIBUTOR = 'sme'))
        GROUP BY 1, 2
      ),

      ALL_AVAILABLE AS (
        SELECT
          S.FEEDID,
          DD.DISPLAYDATE AS DOWNLOAD_ACTIVITY_DATE
        FROM facts.prod.dim_day DD
        CROSS JOIN (SELECT DISTINCT FEEDID FROM STORE_DATA_TOTAL) S
        WHERE DD.DISPLAYDATE > DATEADD('WEEK', -12, CURRENT_DATE())
          AND DD.DISPLAYDATE <= CURRENT_DATE()
      ),

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

      STORE_THRESHOLDS AS (
        SELECT
          FEEDID,
          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 FEEDID
      ),

      EXPANDED_STORE_DATA_SUMMARY AS (
        SELECT
          SDS.FEEDID,
          SDS.DOWNLOAD_ACTIVITY_DATE,
          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.FEEDID = ST.FEEDID
      ),

      MAX_AVAILABLE_DATES AS (
        SELECT
          FEEDID,
          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
      )

      SELECT
        CASE
          WHEN DF.FEEDID = 13 THEN 9999
          WHEN DF.FEEDID = 18 THEN 49602
          WHEN DF.FEEDID = 14 THEN 45302
          ELSE DF.STOREID
        END AS STOREID,
        ESDS.FEEDID,
        ESDS.DOWNLOAD_ACTIVITY_DATE,
        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.FEEDID = MAD.FEEDID
      INNER JOIN facts.prod.dim_feed DF
         ON DF.FEEDID = ESDS.FEEDID
      ORDER BY
        FEEDID, DOWNLOAD_ACTIVITY_DATE) AVAIL
      INNER JOIN INTELLIGENCE.PROD.STORE_SLAS SLAS ON AVAIL.FEEDID = SLAS.FEEDID
      INNER JOIN FACTS.PROD.DIM_LICENSOR LICENSOR ON LICENSOR.FEEDID = SLAS.FEEDID
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      AND LICENSOR.DISTRIBUTOR = 'sme'
      GROUP BY 1, 2, 3, 4, 5
      ),
      FILTERED_SUMMARY AS (
        SELECT *
          FROM STORE_SUMMARY
          WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),
      STORES_AGGREGATED AS (
        SELECT
          FEEDID,
          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 FILTERED_SUMMARY
        GROUP BY 1, 5
      )
        SELECT
          SA.FEEDID,
          DF.FEEDNAME,
          '\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_FEED DF ON SA.FEEDID = DF.FEEDID
        ORDER BY 1
  )
  SELECT
    dt_store_availability_analytics.FEEDID  AS FEEDID,
    dt_store_availability_analytics.FEEDNAME  AS FEEDNAME,
    CASE
    WHEN length(dt_store_availability_analytics.ERROR_DATES)<3 THEN '0'
    ELSE '1'
    END AS OVERALL_STATUS,
    CASE WHEN length(dt_store_availability_analytics.ERROR_DATES)<3
      THEN 'Good'
      ELSE 'Error' END AS OVERALLSTATUS,
    dt_store_availability_analytics.ERROR_DATES  AS ERRORDATES,
    (TO_CHAR(TO_DATE(dt_store_availability_analytics.MAX_COMPLETE_DATE ), 'YYYY-MM-DD')) AS MAXOMPLETEDDATE,
    '12' AS PRIORITYRANKINGSORT,
    '' AS PRIORITYRANKING
    FROM dt_store_availability_analytics
    FETCH NEXT 500 ROWS ONLY)
SELECT
  dt_store_availability_analytics_sme.FEEDID  AS FEEDID,
  dt_store_availability_analytics_sme.FEEDNAME  AS FEEDNAME,
  dt_store_availability_analytics_sme.OVERALL_STATUS AS OVERALL_STATUS,
  dt_store_availability_analytics_sme.OVERALLSTATUS  AS OVERALLSTATUS,
  dt_store_availability_analytics_sme.ERRORDATES  AS ERRORDATES,
      (TO_CHAR(TO_DATE(dt_store_availability_analytics_sme.MAXOMPLETEDDATE ), 'YYYY-MM-DD')) AS MAXCOMPLETEDATE,
      dt_store_availability_analytics_sme.PRIORITYRANKINGSORT  AS PRIORITYRANKINGSORT,
  dt_store_availability_analytics_sme.PRIORITYRANKING  AS PRIORITYRANKING
FROM dt_store_availability_analytics_sme
FETCH NEXT 500 ROWS ONLY)

select *
from table1
union all
select *
from table2;;
```