SQL Filter - filtering with combination of multiple colums

Question

table1

person    | zipcode     | timestamp               | event  | device
--------------------------------------------------|--------|------
amar      | 11111       | 2016-09-28 20:05:03.001 | email  | phone
akbar     | 11111       | 2016-09-28 20:05:03.001 | email  | phone  
antony    | 11111       | 2016-09-28 20:07:03.001 | chat   | pc
amar      | 11111       | 2016-09-28 20:08:03.001 | email  | phone
amar      | 11111       | 2016-09-28 20:08:03.001 | chat   | phone
amar      | 22222       | 2016-09-28 20:09:03.001 | email  | phone
akbar     | 22222       | 2016-09-28 20:10:03.001 | email  | phone  
antony    | 22222       | 2016-09-28 20:10:03.001 | chat   | phone
amar      | 11111       | 2016-09-28 21:05:03.001 | email  | phone
akbar     | 11111       | 2016-09-28 21:05:03.001 | email  | phone  
antony    | 11111       | 2016-09-28 21:07:03.001 | chat   | phone

Output desired - records for all zipcodes for all hours where records with pc device is present

output table for above example

The only qualifying zipcode, timestamp_hour combination is 11111, 2016-09-28 20

person    | zipcode     | timestamp               | event  | device
--------------------------------------------------|--------|------
amar      | 11111       | 2016-09-28 20:05:03.001 | email  | phone
akbar     | 11111       | 2016-09-28 20:05:03.001 | email  | phone  
antony    | 11111       | 2016-09-28 20:07:03.001 | chat   | pc
amar      | 11111       | 2016-09-28 20:08:03.001 | email  | phone
amar      | 11111       | 2016-09-28 20:08:03.001 | chat   | phone

pseudocode

create temp table of zipcode, timestamp_hour combination with filter device='pc' to get qualifying zipcode, timestamp_hour list
apply #1 as a filter with table1

For #1 above, the code i've is as below , need help with #2

with zipcode_hour as (
  select zipcode,   date_trunc(`hour`, timestamp) as timestamp_hour
      from table1
      where device = 'pc'
      group by zipcode, timestamp_hour
)

NzGuy · Accepted Answer · 2016-10-22 05:16:33Z

2

You can use the below query

SELECT person
    ,zipcode
    ,timestamp1
    ,event
    ,device
FROM table1 a
WHERE EXISTS (
        SELECT 1
        FROM table1 b
        WHERE b.device = 'pc'
            AND date_trunc('hour', a.timestamp1) = date_trunc('hour', b.timestamp1)
            AND a.zipcode = b.zipcode
        );

Complete result SQLFiddle

answered Oct 22, 2016 at 5:16

NzGuy

3973 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Olaf Dietsche · Accepted Answer · 2016-10-22 14:46:00Z

0

You can filter with a self join and where clause

select distinct t1.person, t1.zipcode, t1."timestamp", t1.event, t1.device
from table1 t1
join table1 t2 on t2.zipcode = t1.zipcode
               and date_trunc('hour', t2."timestamp") = date_trunc('hour', t1."timestamp")
where t2.device = 'pc'

This looks for all entries t1.*, t2.device having the same zipcode and hour, but keeps only the ones where t2.device is pc. The distinct keyword is needed to weed out duplicate entries.

answered Oct 22, 2016 at 14:46

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Collectives™ on Stack Overflow

SQL Filter - filtering with combination of multiple colums

table1

output table for above example

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

table1

output table for above example

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related