I would like to find out the average time that a client waits on a response from a project manager in a chat conversation. I need to make sure it only calculates the waiting time for the last client message…
I have a table having below schema Item:Varchar Date:Date Quantity:Float trasactionid:int Sample Data: Item Date Quantity transactionid Part1 01-01-2023 10 3 Part1 01-01-2023 15 5 Part1 01-01-2023 17 2 Part1 01-01-2023 13 6 Part1 02-01-2023 13 7 Part1 02-01-2023 1…
I have the following SQL statement. It selects the visit count (vc) for a particular date (vd) SELECT count(vid) vc, date(visitwhen) vd FROM book_visit where date(visitwhen) >=date_sub(now(), interval 14 day) group by vd order by vd desc This selects a…
I have this SQL query: SELECT COUNT(DISTINCT CustomerName) over( ORDER BY OrderTimestamp RANGE BETWEEN INTERVAL 2 hour PRECEDING AND CURRENT ROW ) AS count_per_time FROM Orders WHERE CustomerName IS NOT null AND CustomerName != '' but it doesn't work because…
I am trying to add another column called session_id. I want to rank according to the condition that if the time difference between the date_time is more than 30 minutes, then that will be counted as another session. Here is…
I was wondering why this line that is underlined in red isnt AVG(tenure) OVER() AS avg_tenure? The output table is correct the way it is below, but not sure when we should use AVG() normally vs. AVG() OVER() as a…
Example table with one column tmp_year_test.year and values: year 2010 2011 2012 And now the logic -- 2010 - OK select max(year) over () as max_year from tmp_year_test where year = 2010 -- let's put it in the view create…
I would like to apply window function between times that beyond midnight. The following is a part of example data of per-minute values one stock. Actually it does exists more longer that the example. Data type of datetime is timestamptz…
With this following schema CREATE TABLE test ( id INT , is_true BOOL ); INSERT INTO test (id,is_true) VALUES (1,FALSE); INSERT INTO test (id,is_true) VALUES (2,FALSE); INSERT INTO test (id,is_true) VALUES (3,TRUE); INSERT INTO test (id,is_true) VALUES (4,FALSE); I make…
I have the table below: create table tablex (fileID int, file varchar(10), name varchar(10)); insert into tablex (fileID, file, name) values (1, 'file1' , 'AAA'),(1, 'file1' , 'AAA'),(1, 'file1' , 'AAA'),(2, 'file2' , 'AAA'),(2, 'file2' , 'AAA'),(2, 'file2' , 'AAA'),(1,…