question

![alt text][1] I am having a bit of trouble with a SQL query. I have five tables (shown above), and I need to get the churn at a given month M (where "churn" is the quantity of sellers whose the last ad inputted into the website is more than X days old in any category). I need to present the churn of the first month of the year grouped by main_category and region. The churn depends on the category we are looking at: - For 'realstate' and 'vehicles and Boats' we have churn of 180 days - For the other main categories the churn is 90 days The query I managed to write is: SELECT cat_main.category_main_name CATEGORIA_PRINCIPAL, cat.category_name CATEGORIA_SECUNDÁRIA, reg.region_name REGIAO, max(ad.creation_date) DATA_ULTIMO_ANUNCIO, (CASE WHEN(cat_main.category_main_name in('Imóveis', 'Veículos e barcos') ) then 'churn igual a 180 dias' ELSE 'churn igual a 90 dias' END ) AS CHURN_MES FROM ANUNCIO ad ,seller sel ,region reg ,category cat ,category_main cat_main WHERE ad.seller_id_fk = sel.seller_id_pk AND ad.region_id_fk = reg.region_id_pk AND ad.category_id_fk = cat.category_id_pk AND cat.category_main_id_fk = cat_main.category_main_id_pk AND to_date(ad.creation_date,'DD/MM/YYYY') BETWEEN TO_CHAR('01/01/2015') AND TO_CHAR('31/01/2015') GROUP BY cat.category_name, reg.region_name, ad.creation_date; Could any one please help me :) ---------- [1]: /storage/temp/2631-capture.png