select count(*) as number_clicks, to_char(l.click_time,'J') as cur_date,
p.category_id
from mail_link_clicks l, category_object_map cm, categories c, categories p
where l.link_id = :link_id
and l.mail_id = :mail_job_id
and cm.object_id = l.user_id
and cm.category_id = c.category_id
and c.tree_id = :tree_id
and c.parent_id = p.category_id
group by cur_date , p.category_id
order by cur_date ,p.category_id
SELECT category_id as cur_category_id FROM categories WHERE tree_id = :tree_id AND parent_id is null
select count(*) as number_clicks, to_char(l.click_time,'DD MM YYYY HH24') as cur_ns_set_key,
p.category_id
from mail_link_clicks l, category_object_map cm, categories c, categories p
where l.link_id = :link_id
and l.mail_id = :mail_job_id
and l.click_time >= to_timestamp(:time_stamp_start , 'DD MM YYYY HH24')
and l.click_time < to_timestamp(:time_stamp_end , 'DD MM YYYY HH24')
and cm.object_id = l.user_id
and cm.category_id = c.category_id
and c.tree_id = :tree_id
and c.parent_id = p.category_id
group by cur_ns_set_key , p.category_id
order by p.category_id, cur_ns_set_key