# View: dt_store_availability_tiktok

**View Name:** dt_store_availability_tiktok
**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.view.lkml`

## Overview

- **File Size:** 23465 bytes
- **Lines of Code:** 519
- **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 |
|------|------|
| `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
                    MISSING_DATE AS MISSING_DATES,
                    INCOMPLETE_DATE AS INCOMPLETE_DATES,
                    DUPLICATED_DATE AS DUPLICATED_DATES,
                    ERROR_DATES_THRESHOLD,
                    STORE_HIGH_WATERMARK
                  FROM FILTERED_SUMMARY
                  GROUP BY 1, 2, 3, 4, 5
                )

                  SELECT
                    'TikTok Top Hashtag' AS FEEDNAME,
                    '\n'||
                    CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
                      THEN '-Missing report for: ' || TO_VARCHAR(MISSING_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
                      THEN '-Incomplete Dates: ' || TO_VARCHAR(INCOMPLETE_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
                      THEN '-Duplicated Dates: ' || 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
                    MISSING_DATE AS MISSING_DATES,
                    INCOMPLETE_DATE AS INCOMPLETE_DATES,
                    DUPLICATED_DATE AS DUPLICATED_DATES,
                    ERROR_DATES_THRESHOLD,
                    STORE_HIGH_WATERMARK
                  FROM FILTERED_SUMMARY
                  GROUP BY 1, 2, 3, 4, 5
                )
                  SELECT
                    'TikTok Top Song' AS FEEDNAME,
                    '\n'||
                    CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
                      THEN '-Missing report for: ' || TO_VARCHAR(MISSING_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
                      THEN '-Incomplete Dates: ' || TO_VARCHAR(INCOMPLETE_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
                      THEN '-Duplicated Dates: ' || 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
                    MISSING_DATE AS MISSING_DATES,
                    INCOMPLETE_DATE AS INCOMPLETE_DATES,
                    DUPLICATED_DATE AS DUPLICATED_DATES,
                    ERROR_DATES_THRESHOLD,
                    STORE_HIGH_WATERMARK
                  FROM FILTERED_SUMMARY
                  GROUP BY 1, 2, 3, 4, 5
                )

                  SELECT
                    'TikTok Daily' AS FEEDNAME,
                    '\n'||
                    CASE WHEN TO_VARCHAR(MISSING_DATES) != ''
                      THEN '-Missing report for: ' || TO_VARCHAR(MISSING_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(INCOMPLETE_DATES)  != ''
                      THEN '-Incomplete Dates: ' || TO_VARCHAR(INCOMPLETE_DATES) || '\n'
                      ELSE '' END ||
                    CASE WHEN TO_VARCHAR(DUPLICATED_DATES) != ''
                      THEN '-Duplicated Dates: ' || 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;;
```