WITH transport AS (
SELECT --top 5
date_id,booking_code ,city_id,taxi_type_id,driver_id
FROM dwh.f_transport
WHERE date_id = 20220321
),
rating AS (
SELECT date_id, driver_id,booking_code,response ,dax_rating ,response_type
FROM dwh.f_ratings
WHERE date_id = 20220321
)
SELECT
c.city_name
,tt.taxi_type_simple as vertical_name
,tt.taxi_type_name
,r.dax_rating
,r.response
,d.activated as driver_activated
,COUNT(DISTINCT t.booking_code) as total_responses
,r.driver_id
FROM transport t
INNER JOIN rating r
ON t.booking_code = r.booking_code
INNER JOIN dwh.d_city c
ON c.city_id = t.city_id
INNER JOIN dwh.d_taxi_type tt
ON t.taxi_type_id = tt.taxi_type_id
INNER JOIN dwh.d_driver d
ON d.driver_id = t.driver_id
WHERE r.date_id= 20220321
AND r.response_type in ('NEW_IMPROVEMENT' ,'IMPROVEMENT')
AND r.dax_rating < 5
AND c.country_id = 9
GROUP BY 1,2,3,4,5,6,8 ;