Prix d'un massage : ce qu'affichent les praticiens sur Pausila

• Caen : 65 €
• Dijon : 62 €
• Lille : 64 €
• Lyon : 68 €
• Marseille : 65 €
• Montpellier : 69 €
• Niort : 69 €
• Paris : 68 €
• Rennes : 69 €

with visible as (
  select id from public.practitioner_public_visibility where visible
), s as (
  select ps.practitioner_id, ps.price_eur::numeric as price, ps.specialty_slug,
         ps.available_for_reception as cabinet, ps.available_for_travel as domicile,
         p.code_postal,
         case when ps.duration_min between 25 and 35 then 30
              when ps.duration_min between 40 and 50 then 45
              when ps.duration_min between 55 and 65 then 60
              when ps.duration_min between 70 and 80 then 75
              when ps.duration_min between 85 and 95 then 90
              when ps.duration_min between 115 and 125 then 120 end as duree
  from public.practitioner_services ps
  join visible v on v.id = ps.practitioner_id
  join public.practitioners p on p.id = ps.practitioner_id
  where ps.is_active is not false and ps.price_currency = 'EUR'
    and ps.price_eur between 15 and 400
), valeurs as (
  -- Une valeur par praticien et par segment : la médiane de ses prestations.
  select 'duree' as segment, duree::text as cle, practitioner_id,
         percentile_cont(0.5) within group (order by price) as p
  from s where duree is not null group by 2, 3
  union all
  select 'pratique', specialty_slug, practitioner_id,
         percentile_cont(0.5) within group (order by price)
  from s where duree = 60 and coalesce(specialty_slug, '') not in ('', 'custom', 'autre')
  group by 2, 3
  union all
  select 'zone',
         case when code_postal ~ '^[0-9]{5}$'
                   and left(code_postal, 2) in ('75', '77', '78', '91', '92', '93', '94', '95')
                then 'ile_de_france'
              when code_postal ~ '^[0-9]{5}$' then 'hors_ile_de_france' end,
         practitioner_id, percentile_cont(0.5) within group (order by price)
  from s where duree = 60 group by 2, 3
  union all
  select 'lieu',
         case when cabinet and not domicile then 'cabinet'
              when cabinet and domicile then 'les_deux'
              when domicile and not cabinet then 'domicile' end,
         practitioner_id, percentile_cont(0.5) within group (order by price)
  from s where duree = 60 group by 2, 3
)
select segment, cle, count(*) as praticiens,
  round(percentile_cont(0.25) within group (order by p)::numeric) as q1,
  round(percentile_cont(0.5) within group (order by p)::numeric) as mediane,
  round(percentile_cont(0.75) within group (order by p)::numeric) as q3
from valeurs
where cle is not null
group by 1, 2
having count(*) >= 15
order by 1, 3 desc;