# View: dt_store_availability_tiktok_reduced

**View Name:** dt_store_availability_tiktok_reduced
**Table Source:** `Derived from: All_Available, Prod.Dim_Day, Prod.Staging_Raw_Tiktok, Prod.Staging_Raw_Tiktok_Trends_Tophashtag, Prod.Staging_Raw_Tiktok_Trends_Topsong + 3 more`
**File Path:** `dt_store_availability_tiktok_reduced.view.lkml`

## Overview

- **File Size:** 19834 bytes
- **Lines of Code:** 519
- **Dimensions:** 7
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- ffcd75; background-color: #ffcd75; font-size:100%;">""</p>
- 72c275; background-color: #72c275; font-size:100%;">""</p>

## Dimensions

| Name | Type |
|------|------|
| `feedname` | string |
| `error_dates` | string |
| `max_complete_date` | date |
| `overal_status` | string |
| `threshold` | number |
| `priority_ranking` | number |
| `priority_ranking_sort` | number |

## Derived Table

```sql
sql:WITH tiktokhashtag as (

                WITH dt_store_availability_analytics_tiktok_tophashtag AS (
                  WITH dt_store_availability_analytics AS (
                    WITH STORE_SUMMARY AS (
                      SELECT
                        CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
                          THEN DOWNLOAD_DATE
                          END AS MISSING_DATE,
                        CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
                          THEN DOWNLOAD_DATE
                          END AS INCOMPLETE_DATE,
                        CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
                          THEN DOWNLOAD_DATE
                          END AS DUPLICATED_DATE,
                        STORE_HIGH_WATERMARK,
                        9 AS ERROR_DATES_THRESHOLD
                      FROM (
                        WITH LAST_DOWNLOAD_DATE AS (
                        SELECT
                          DOWNLOAD_DATE
                        FROM FACTS.PROD.STAGING_RAW_TIKTOK_TRENDS_TOPHASHTAG
                        WHERE DOWNLOAD_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
                        ORDER BY DOWNLOAD_DATE DESC
                        FETCH NEXT 1 ROWS ONLY
                      ),
                      STORE_DATA_TOTAL AS (
                        SELECT
                          DOWNLOAD_DATE,
                          SUM(TOTAL_VIEWS) AS UNITS
                        FROM FACTS.PROD.STAGING_RAW_TIKTOK_TRENDS_TOPHASHTAG
                        WHERE DOWNLOAD_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
                        GROUP BY 1
                      ),

      ALL_AVAILABLE AS (
      SELECT
      DD.DISPLAYDATE AS DOWNLOAD_DATE
      FROM facts.prod.dim_day DD
      WHERE DD.DISPLAYDATE > DATEADD('WEEK', -12, CURRENT_DATE())
      AND DD.DISPLAYDATE <= CURRENT_DATE()
      ),

      STORE_DATA_SUMMARY AS (
      SELECT
      A.DOWNLOAD_DATE,
      S.UNITS
      FROM ALL_AVAILABLE A
      LEFT JOIN STORE_DATA_TOTAL S
      ON A.DOWNLOAD_DATE = S.DOWNLOAD_DATE
      ORDER BY 1, 2
      ),

      STORE_THRESHOLDS AS (
      SELECT
      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_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE())
      AND DATEADD('WEEK', -4, CURRENT_DATE())
      ),

      EXPANDED_STORE_DATA_SUMMARY AS (
      SELECT
      SDS.DOWNLOAD_DATE,
      IFF(SUM(CASE WHEN SDS.UNITS IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY DOWNLOAD_DATE DESC) > 0, NULL, 1) 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
      ),

      MAX_AVAILABLE_DATES AS (
      SELECT
      MAX(DOWNLOAD_DATE) AS STORE_HIGH_WATERMARK
      FROM EXPANDED_STORE_DATA_SUMMARY
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NULL
      )

      SELECT
      ESDS.DOWNLOAD_DATE,
      ESDS.MISSING_DATE,
      ESDS.INCOMPLETE_DATE,
      ESDS.DUPLICATED_DATE,
      MAD.STORE_HIGH_WATERMARK,
      LAST_DOWNLOAD_DATE.DOWNLOAD_DATE AS LAST_DOWNLOAD_DATE

      FROM EXPANDED_STORE_DATA_SUMMARY ESDS
      LEFT JOIN MAX_AVAILABLE_DATES MAD
      LEFT JOIN LAST_DOWNLOAD_DATE
      LEFT JOIN STORE_THRESHOLDS STTHR
      ORDER BY
      DOWNLOAD_DATE) AVAIL
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      GROUP BY 1, 2, 3, 4
      ORDER BY MISSING_DATE
      FETCH NEXT 1 ROWS ONLY
      ),
      FILTERED_SUMMARY AS (
      SELECT *
      FROM STORE_SUMMARY
      WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),
      STORES_AGGREGATED AS (
      SELECT
      LISTAGG(monthname(to_date(MISSING_DATE)) || ' ' || ltrim(RIGHT(MISSING_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
      LISTAGG(monthname(to_date(INCOMPLETE_DATE)) || ' ' || ltrim(RIGHT(INCOMPLETE_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
      LISTAGG(monthname(to_date(DUPLICATED_DATE)) || ' ' || ltrim(RIGHT(DUPLICATED_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY DUPLICATED_DATE) AS DUPLICATED_DATES,
      ERROR_DATES_THRESHOLD,
      STORE_HIGH_WATERMARK
      FROM FILTERED_SUMMARY
      GROUP BY 4, 5
      )

      SELECT
      'TikTok Top Hashtag' AS FEEDNAME,
      '\n'||
      CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
      THEN TO_VARCHAR(MISSING_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
      THEN TO_VARCHAR(INCOMPLETE_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
      THEN TO_VARCHAR(DUPLICATED_DATES) || '\n'
      ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE,
      ERROR_DATES_THRESHOLD
      FROM STORES_AGGREGATED SA
      )
      SELECT
      dt_store_availability_analytics.FEEDNAME  AS FEEDNAME,
      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,
      dt_store_availability_analytics.ERROR_DATES_THRESHOLD,
      '12' AS PRIORITYRANKINGSORT,
      '' AS PRIORITYRANKING
      FROM dt_store_availability_analytics
      FETCH NEXT 500 ROWS ONLY)
      SELECT
      dt_store_availability_analytics_tiktok_tophashtag.FEEDNAME  AS FEEDNAME,
      dt_store_availability_analytics_tiktok_tophashtag.OVERALLSTATUS  AS OVERALLSTATUS,
      dt_store_availability_analytics_tiktok_tophashtag.ERRORDATES  AS ERRORDATES,
      (TO_CHAR(TO_DATE(dt_store_availability_analytics_tiktok_tophashtag.MAXOMPLETEDDATE ), 'YYYY-MM-DD')) AS MAXCOMPLETEDATE,
      dt_store_availability_analytics_tiktok_tophashtag.ERROR_DATES_THRESHOLD  AS ERROR_DATES_THRESHOLD,
      dt_store_availability_analytics_tiktok_tophashtag.PRIORITYRANKING  AS PRIORITYRANKING,
      dt_store_availability_analytics_tiktok_tophashtag.PRIORITYRANKINGSORT  AS PRIORITYRANKINGSORT
      FROM dt_store_availability_analytics_tiktok_tophashtag
      FETCH NEXT 500 ROWS ONLY),

      tiktoksong as (

      WITH dt_store_availability_analytics_tiktok_topsong AS (
      WITH dt_store_availability_analytics AS (
      WITH STORE_SUMMARY AS (
      SELECT
      CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS MISSING_DATE,
      CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS INCOMPLETE_DATE,
      CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 9, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS DUPLICATED_DATE,
      STORE_HIGH_WATERMARK,
      9 AS ERROR_DATES_THRESHOLD
      FROM (
      WITH LAST_DOWNLOAD_DATE AS (
      SELECT
      DOWNLOAD_DATE
      FROM FACTS.PROD.STAGING_RAW_TIKTOK_TRENDS_TOPSONG
      WHERE DOWNLOAD_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
      ORDER BY DOWNLOAD_DATE DESC
      FETCH NEXT 1 ROWS ONLY
      ),
      STORE_DATA_TOTAL AS (
      SELECT
      DOWNLOAD_DATE,
      SUM(TOTAL_VIEWS) AS UNITS
      FROM FACTS.PROD.STAGING_RAW_TIKTOK_TRENDS_TOPSONG
      WHERE DOWNLOAD_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE()) AND CURRENT_DATE()
      GROUP BY 1
      ),
      ALL_AVAILABLE AS (
      SELECT
      DD.DISPLAYDATE AS DOWNLOAD_DATE
      FROM facts.prod.dim_day DD
      WHERE DD.DISPLAYDATE > DATEADD('WEEK', -12, CURRENT_DATE())
      AND DD.DISPLAYDATE <= CURRENT_DATE()
      ),
      STORE_DATA_SUMMARY AS (
      SELECT
      A.DOWNLOAD_DATE,
      S.UNITS
      FROM ALL_AVAILABLE A
      LEFT JOIN STORE_DATA_TOTAL S
      ON A.DOWNLOAD_DATE = S.DOWNLOAD_DATE
      ORDER BY 1, 2
      ),
      STORE_THRESHOLDS AS (
      SELECT
      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_DATE BETWEEN DATEADD('WEEK', -12, CURRENT_DATE())
      AND DATEADD('WEEK', -4, CURRENT_DATE())
      ),
      EXPANDED_STORE_DATA_SUMMARY AS (
      SELECT
      SDS.DOWNLOAD_DATE,
      IFF(SUM(CASE WHEN SDS.UNITS IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY DOWNLOAD_DATE DESC) > 0, NULL, 1) 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
      ),
      MAX_AVAILABLE_DATES AS (
      SELECT
      MAX(DOWNLOAD_DATE) AS STORE_HIGH_WATERMARK
      FROM EXPANDED_STORE_DATA_SUMMARY
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NULL
      )
      SELECT
      ESDS.DOWNLOAD_DATE,
      ESDS.MISSING_DATE,
      ESDS.INCOMPLETE_DATE,
      ESDS.DUPLICATED_DATE,
      MAD.STORE_HIGH_WATERMARK,
      LAST_DOWNLOAD_DATE.DOWNLOAD_DATE AS LAST_DOWNLOAD_DATE
      FROM EXPANDED_STORE_DATA_SUMMARY ESDS
      LEFT JOIN MAX_AVAILABLE_DATES MAD
      LEFT JOIN LAST_DOWNLOAD_DATE
      ORDER BY
      DOWNLOAD_DATE) AVAIL
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      GROUP BY 1, 2, 3, 4
      ORDER BY MISSING_DATE
      FETCH NEXT 1 ROWS ONLY
      ),
      FILTERED_SUMMARY AS (
      SELECT *
      FROM STORE_SUMMARY
      WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),
      STORES_AGGREGATED AS (
      SELECT
      LISTAGG(monthname(to_date(MISSING_DATE)) || ' ' || ltrim(RIGHT(MISSING_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
      LISTAGG(monthname(to_date(INCOMPLETE_DATE)) || ' ' || ltrim(RIGHT(INCOMPLETE_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
      LISTAGG(monthname(to_date(DUPLICATED_DATE)) || ' ' || ltrim(RIGHT(DUPLICATED_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY DUPLICATED_DATE) AS DUPLICATED_DATES,
      ERROR_DATES_THRESHOLD,
      STORE_HIGH_WATERMARK
      FROM FILTERED_SUMMARY
      GROUP BY 4, 5
      )
      SELECT
      'TikTok Top Song' AS FEEDNAME,
      '\n'||
      CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
      THEN TO_VARCHAR(MISSING_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
      THEN TO_VARCHAR(INCOMPLETE_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
      THEN TO_VARCHAR(DUPLICATED_DATES) || '\n'
      ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE,
      ERROR_DATES_THRESHOLD
      FROM STORES_AGGREGATED SA
      )
      SELECT
      dt_store_availability_analytics.FEEDNAME  AS FEEDNAME,
      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,
      dt_store_availability_analytics.ERROR_DATES_THRESHOLD,
      '12' AS PRIORITYRANKINGSORT,
      '' AS PRIORITYRANKING
      FROM dt_store_availability_analytics
      FETCH NEXT 500 ROWS ONLY)
      SELECT
      dt_store_availability_analytics_tiktok_topsong.FEEDNAME  AS FEEDNAME,
      dt_store_availability_analytics_tiktok_topsong.OVERALLSTATUS  AS OVERALLSTATUS,
      dt_store_availability_analytics_tiktok_topsong.ERRORDATES  AS ERRORDATES,
      (TO_CHAR(TO_DATE(dt_store_availability_analytics_tiktok_topsong.MAXOMPLETEDDATE ), 'YYYY-MM-DD')) AS MAXCOMPLETEDATE,
      dt_store_availability_analytics_tiktok_topsong.ERROR_DATES_THRESHOLD  AS ERROR_DATES_THRESHOLD,
      dt_store_availability_analytics_tiktok_topsong.PRIORITYRANKING  AS PRIORITYRANKING,
      dt_store_availability_analytics_tiktok_topsong.PRIORITYRANKINGSORT  AS PRIORITYRANKINGSORT
      FROM dt_store_availability_analytics_tiktok_topsong
      FETCH NEXT 500 ROWS ONLY),

      tiktok_daily AS (
      WITH dt_store_availability_analytics_tiktok_daily AS (
      WITH dt_store_availability_analytics AS (
      WITH STORE_SUMMARY AS (
      SELECT
      CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 4, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS MISSING_DATE,
      CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 4, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS INCOMPLETE_DATE,
      CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_DATE > DATEADD('DAY', 4, LAST_DOWNLOAD_DATE)
      THEN DOWNLOAD_DATE
      END AS DUPLICATED_DATE,
      4 AS ERROR_DATES_THRESHOLD,
      STORE_HIGH_WATERMARK
      FROM (
      WITH LAST_DOWNLOAD_DATE AS (
      SELECT
      DOWNLOAD_DATE
      FROM FACTS.PROD.STAGING_RAW_TIKTOK
      WHERE DOWNLOAD_DATE BETWEEN '2021-06-10' AND CURRENT_DATE()
      ORDER BY DOWNLOAD_DATE DESC
      FETCH NEXT 1 ROWS ONLY
      ),
      STORE_DATA_TOTAL AS (
      SELECT
      DOWNLOAD_DATE,
      SUM(VIDEO_VIEWS) AS UNITS
      FROM FACTS.PROD.STAGING_RAW_TIKTOK
      WHERE DOWNLOAD_DATE BETWEEN '2021-06-10' AND CURRENT_DATE()
      GROUP BY 1
      ),

      ALL_AVAILABLE AS (
      SELECT
      DD.DISPLAYDATE AS DOWNLOAD_DATE
      FROM facts.prod.dim_day DD
      WHERE DD.DISPLAYDATE >= '2021-06-10'
      AND DD.DISPLAYDATE <= CURRENT_DATE()
      ),

      STORE_DATA_SUMMARY AS (
      SELECT
      A.DOWNLOAD_DATE,
      S.UNITS
      FROM ALL_AVAILABLE A
      LEFT JOIN STORE_DATA_TOTAL S
      ON A.DOWNLOAD_DATE = S.DOWNLOAD_DATE
      ORDER BY 1, 2
      ),

      STORE_THRESHOLDS AS (
      SELECT
      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 + 4*IQR AS MAX_THRESHOLD
      FROM STORE_DATA_SUMMARY
      WHERE DOWNLOAD_DATE BETWEEN DATEADD('DAY', -14, CURRENT_DATE())
      AND DATEADD('DAY', -1, CURRENT_DATE())
      ),

      EXPANDED_STORE_DATA_SUMMARY AS (
      SELECT
      SDS.DOWNLOAD_DATE,
      IFF(SUM(CASE WHEN SDS.UNITS IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY DOWNLOAD_DATE DESC) > 0, NULL, 1) 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
      ),

      MAX_AVAILABLE_DATES AS (
      SELECT
      MAX(DOWNLOAD_DATE) AS STORE_HIGH_WATERMARK
      FROM EXPANDED_STORE_DATA_SUMMARY
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NULL
      )

      SELECT
      ESDS.DOWNLOAD_DATE,
      ESDS.MISSING_DATE,
      ESDS.INCOMPLETE_DATE,
      ESDS.DUPLICATED_DATE,
      MAD.STORE_HIGH_WATERMARK,
      LAST_DOWNLOAD_DATE.DOWNLOAD_DATE AS LAST_DOWNLOAD_DATE

      FROM EXPANDED_STORE_DATA_SUMMARY ESDS
      LEFT JOIN MAX_AVAILABLE_DATES MAD
      LEFT JOIN LAST_DOWNLOAD_DATE
      ORDER BY
      DOWNLOAD_DATE) AVAIL
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL
      GROUP BY 1, 2, 3, 4, 5
      ORDER BY MISSING_DATE
      FETCH NEXT 1 ROWS ONLY
      ),
      FILTERED_SUMMARY AS (
      SELECT *
      FROM STORE_SUMMARY
      WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),
      STORES_AGGREGATED AS (
      SELECT
      LISTAGG(monthname(to_date(MISSING_DATE)) || ' ' || ltrim(RIGHT(MISSING_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
      LISTAGG(monthname(to_date(INCOMPLETE_DATE)) || ' ' || ltrim(RIGHT(INCOMPLETE_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
      LISTAGG(monthname(to_date(DUPLICATED_DATE)) || ' ' || ltrim(RIGHT(DUPLICATED_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY DUPLICATED_DATE) AS DUPLICATED_DATES,
      ERROR_DATES_THRESHOLD,
      STORE_HIGH_WATERMARK
      FROM FILTERED_SUMMARY
      GROUP BY 4, 5
      )

      SELECT
      'TikTok Daily' AS FEEDNAME,
      '\n'||
      CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
      THEN TO_VARCHAR(MISSING_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
      THEN TO_VARCHAR(INCOMPLETE_DATES) || '\n'
      ELSE '' END ||
      CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
      THEN TO_VARCHAR(DUPLICATED_DATES) || '\n'
      ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE,
      ERROR_DATES_THRESHOLD
      FROM STORES_AGGREGATED SA
      )
      SELECT
      dt_store_availability_analytics.FEEDNAME  AS FEEDNAME,
      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,
      dt_store_availability_analytics.ERROR_DATES_THRESHOLD,
      '12' AS PRIORITYRANKINGSORT,
      '' AS PRIORITYRANKING
      FROM dt_store_availability_analytics
      FETCH NEXT 500 ROWS ONLY)
      SELECT
      dt_store_availability_analytics_tiktok_daily.FEEDNAME  AS FEEDNAME,
      dt_store_availability_analytics_tiktok_daily.OVERALLSTATUS  AS OVERALLSTATUS,
      dt_store_availability_analytics_tiktok_daily.ERRORDATES  AS ERRORDATES,
      (TO_CHAR(TO_DATE(dt_store_availability_analytics_tiktok_daily.MAXOMPLETEDDATE ), 'YYYY-MM-DD')) AS MAXCOMPLETEDATE,
      dt_store_availability_analytics_tiktok_daily.ERROR_DATES_THRESHOLD  AS ERROR_DATES_THRESHOLD,
      dt_store_availability_analytics_tiktok_daily.PRIORITYRANKING  AS PRIORITYRANKING,
      dt_store_availability_analytics_tiktok_daily.PRIORITYRANKINGSORT  AS PRIORITYRANKINGSORT
      FROM dt_store_availability_analytics_tiktok_daily
      FETCH NEXT 500 ROWS ONLY
      )

      select *
      from tiktokhashtag
      union all
      select *
      from tiktoksong
      union all
      select * from tiktok_daily;;
```