0

How to display all Detections in the same second of time for different User_ID when users> 2?

CREATE TABLE "detections" (
    "date"  text,
    "device_id" text,
    "user_id"   text,

);

2020-01-01 00:00:35 13174   18817
2020-01-01 00:01:31 12405   17646
2020-01-01 00:05:17 10604   15190
2020-01-01 00:06:16 13174   18817
2020-01-01 00:07:08 4537    6037
2020-01-01 00:07:46 12405   17646
2020-01-01 00:10:05 12405   17646
2020-01-01 00:10:05 9848    7183
2020-01-01 00:13:26 13174   18817
2020-01-01 00:18:32 13174   18817

link to original datebase [https://github.com/credo-science/Windows-Tools/blob/master/Credo%20NONfiltered%20data%20%26%20EathQake/no_filtr_no_content.sqlite]

Marek
  • 31
  • 7
  • In your sample data I don't see rows having the *same second of time for different User_ID when users> 2*. Post sample data that satisfy your conditions (and not) and expected results to clarify. – forpas Jun 03 '20 at 18:01
  • Sorry, these are the first few lines from the base. The database has 180,000 records from 1st January. – Marek Jun 03 '20 at 18:06
  • Does this answer your question? [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) – Izana Jun 03 '20 at 23:01

1 Answers1

1

This query:

select date
from detections
group by date
having count(distinct user_id) > 2

returns all the dates that satisfy your condition.
You can use it with the operator IN to get all the rows from the table:

select * from detections
where date in (
  select date
  from detections
  group by date
  having count(distinct user_id) > 2
)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • I really care about displaying the number of these multiplexes and uder_ID data. I need this information. Who and how many were there. It is possible? And can you add the ability to change from one second to any range, eg Was there several detections in 5 seconds gape? – Marek Jun 03 '20 at 18:40
  • This is why you should post sample data with rows that satisfy the conditions and expected results. – forpas Jun 03 '20 at 18:48
  • https://github.com/credo-science/Windows-Tools/blob/master/Credo%20NONfiltered%20data%20%26%20EathQake/no_filtr_no_content.sqlite – Marek Jun 03 '20 at 18:50