0

Basically my SQL code is trying to get the percentage of people that stayed after 5:15 PM to work. These two tables represent one that stayed later after 5:15 PM and one that left early before 5:15. Below is my query:

WITH people_that_left_early AS ( 
SELECT username, MAX(created_at) AS timestamp
FROM doorlogs
WHERE created_at <= '2017-07-03 05:15:00 PM'
AND created_at >= '2017-07-03 06:00:00 AM'
GROUP BY username),

WITH people_that_stayed_late AS (
SELECT a.username, a.event, b.timestamp
FROM doorlogs a 
INNER JOIN people_that_left_early b
ON a.username = b.username
WHERE a.created_at = b.timestamp
AND event <> 'X')

SELECT COUNT(a.username), COUNT(b.username) 
FROM people_that_left_early a 
LEFT JOIN people_that_stayed_late b
ON a.username = b.username;

The second table people_that_stayed_late is basically the people that didn't exit the building after 5:15 PM on 3 JUL 2017. Every time I run the query I get the error:

ERROR:  syntax error at or near "WITH"
LINE 7: WITH people_that_stayed_late AS (

I know I'm using the first table in my second table but I don't understand why its giving me this error. Also I didn't do the calculation for getting the percentage yet cause I wanted to see if I get the right numbers for who went to work on that specific day and who stayed in late after 5:15 PM on JUL 3 2017.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Kenkuts
  • 59
  • 1
  • 11

1 Answers1

1

Just omit the WITH keyword the second time.

The syntax is

WITH [ RECURSIVE ] name1 AS (...),
                   name2 AS (...)
                   [ , ... ]
SELECT ...
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Oh wow I didn't even know that. I always used WITH even after making a diff table and try to compare them with a select statement at the end. – Kenkuts Mar 04 '19 at 05:09