Hi All,
I am really in need of help in querying the single table. The table I need to query will have thousands of data and due to limitations and the masking, I cannot post the DDLs and DMLs and data dump, Apologies for this. I will try to be as detailed as possible
DB : Oracle 12C and SQL Developer
Table: Caller_Details
| SERIAL_NUMBER | CALLER_ID | TAG | IS_DELETED | TIME |
|---|
| 1 | 444 | Active | 0 | 2020-06-04 14:27:52.390 |
| 2 | 666 | Active | 0 | 2020-06-04 14:28:24.440 |
| 3 | 111 | Passive | 0 | 2020-06-04 14:28:27.217 |
| 4 | 222 | Active | 1 | 2020-06-04 14:28:27.600 |
| 5 | 333 | Passive | 0 | 2020-06-04 14:28:41.743 |
| 6 | 111 | Active | 0 | 2020-06-04 14:28:57.020 |
| 7 | 222 | Active | 0 | 2020-06-04 14:28:57.563 |
| 8 | 222 | Passive | 0 | 2020-06-04 14:29:00.187 |
| 9 | 555 | Passive | 0 | 2020-06-04 14:29:08.300 |
Data Types:
SERIAL_NUMBER = Integer and Primary Key
CALLER_ID = Integer
TAG = Varchar
IS_DELETED = Integer
TIME = Timestamp
Requirement:
I need to get the count of callers i.e count(distinct CALLER_ID) who are registered to both 'Active' as well as 'Passive' and out of these two, the registration to 'Active' is first (based on Time) which is not deleted.
Note:
IS_DELETED column specifies if the registration exists or marked as deleted.
0 = Not Deleted
1 = Deleted
I tried using rank function to check if I could order the records, but not really sure what next I should be doing. Appreciate any help here.
select count(caller_id) from (
select tag, caller_id, time,
rank() over (order by time asc) rnk_on_time
from caller_details
where is_deleted=0
and tag in ('Active', 'Passive')
) dummy