question

igorag avatar image
igorag asked

Need help with sql query that finds the churn in a period

![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
sqlaggregatespostgresql
capture.png (91.6 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you tell us what problem you're having with your existing query? Would it help to split your query into two separate queries? The first looking at realestate and then UNION ALLed to a query that considers the rest?
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.