1
SELECT 
(users.created_at::timestamp - invitations.created_at::timestamp) as elapsed,
percentile_disc(0.50)
      WITHIN GROUP (ORDER BY elapsed) as median
 FROM invitations
 join users on users.id = invitations.user_id

Trying to find out the median wait time between the creation of an invitation and the acceptance of it by the user. This code generates an error:

ERROR: column "elapsed" does not exist.

How can I repair it?

JasonGenX
  • 4,952
  • 27
  • 106
  • 198

1 Answers1

2

You can't use an alias on the same level where you define it. You need to wrap this in a derived table:

select elapsed, 
       percentile_disc(0.50) WITHIN GROUP (ORDER BY elapsed) as median
from (
  SELECT users.created_at::timestamp - invitations.created_at::timestamp as elapsed,
  FROM invitations
    join users on users.id = invitations.user_id
) t;