| ID | Status | Date |
|---|---|---|
| 1 | Online | 2022-06-31 |
| 1 | Offline | 2022-07-28 |
| 2 | Online | 2022-08-01 |
| 3 | Online | 2022-07-03 |
| 3 | Stationary | 2022-07-05 |
| 4 | Offline | 2022-05-02 |
| 5 | Online | 2022-04-04 |
| 5 | Online | 2022-04-06 |
The output I’m looking for in SQL/BigQuery
| ID | Status | Date |
|---|---|---|
| 2 | Online | 2022-08-01 |
| 5 | Online | 2022-04-04 |
| 5 | Online | 2022-04-06 |
So what SQL query I would want to return is the rows for ID 2 & 5 as they both only have Online for that ID. 1 & 3 should not be included even though they have one “online” status. I am only looking for IDs which have Online as their only status.
I just need a query for the ID where the only status for that ID is Online.
I’m assuming I need to use the qualify function but not sure how to go about this, thanks for the help in advance
Advertisement
Answer
Consider also below option
select * from your_table qualify countif(status != 'Online') over (partition by id)= 0
if applied to sample data in your question – output is
