SQL Query for Counting Rows Based on Dates and Joins

The query above selects the following columns:

1. '2019-02-20' AS dt: This column represents a static value of '2019-02-20' and is given the alias 'dt'.

2. CONCAT(DATE_ADD(NEXT_DAY('2019-02-20', 'MO'), INTERVAL -7 DAY),'_',DATE_ADD(NEXT_DAY('2019-02-20', 'MO'), INTERVAL -1 DAY)) AS wk_dt: This column concatenates two dates using the CONCAT function. The first date is obtained by subtracting 7 days from the next Monday after '2019-02-20', and the second date is obtained by subtracting 1 day from the next Monday after '2019-02-20'. The resulting concatenation is given the alias 'wk_dt'.

3. COUNT(*): This column counts the number of rows in the subquery result set.

The query uses a subquery to find the mid_ids that meet certain conditions. It joins three subqueries (t1, t2, and t3) using LEFT JOINs and checks for NULL values in t2 and t3. The conditions for each subquery are as follows:

1. Subquery t1:

   • Selects mid_ids from the table dws_uv_detail_wk where wk_dt matches the concatenated date obtained in the outer query.

2. Subquery t2:

   • Selects mid_ids from the table dws_new_mid_day where create_date is between the two dates calculated in the outer query.

3. Subquery t3:

   • Selects mid_ids from the table dws_uv_detail_wk where wk_dt matches the concatenated date obtained in the outer query, but with different date calculations.

The WHERE clause in the main query filters the results to only include mid_ids that are NULL in both t2 and t3. Finally, the COUNT(*) function is used to count the number of rows in the subquery result set.