I am trying to get display those theater and circuits details where if a circuit with audi number 1, have screen format as imax today but 1 month back it was dolby.

I tried simple query but is was going into slow query.

SELECT distinct circuit_name, theater_name, auditorium, screen_format
FROM table_name
WHERE date_sh between ‘2024-07-12’ and ‘2024-08-12’
GROUP BY theater_name, auditorium, screen_format
HAVING COUNT(DISTINCT screen_format) > 1;

So I tried to use CTE but it is showing error:

WITH shows AS (
SELECT theater_name, auditorium, screen_format
FROM table_name
WHERE date_sh BETWEEN ‘2024-07-12’ AND ‘2024-08-12’
)
SELECT distinct circuit_name, theater_name, auditorium
FROM movies_shows
WHERE (theater_name, auditorium) IN (
SELECT theater_name, auditorium
FROM table_name
GROUP BY theater_name, auditorium
HAVING COUNT(DISTINCT screen_format) > 1
);

Error