I have a chat system that handles group chats. A conversation can have many participants and the only distinction between group and non group chats is a non group chat will only have 2 participants. I want a query to check if a non group chat exists between 2 users. Conversations table Participants table To c…
Postgresql: How do I use dynamic values when searching jsonb array of objects?
I am currently trying to build a query for finding specfic object within a jsonb array. I have the following query which works fine if I used a hard coded string for the “game” value e.g. However, if I use a dynamic value like I currently do for username, I get invalid json syntax error. e.g. How …
How to get the closing time of day in timing schedule that spans multiple days
Assume I have the following table called Timing: Obviously each row represents a shift in a specific day. A day can have non-overlapping multiple shifts. If a shift spans the next day it will be splitted at midnight, and the second half would have a parent id of the first half (as you can see in row 24 and 31…
How do I add a column that counts unique instances of another columns data
I am an end user that does not work with SQL everyday. However I am sometimes asked to add modify existing queries to provide new data. Currently I have a query that calculates (counts) the occurrences of a column labeled (Qid) and displays the top 5 based on count. It was necessary to use a select statement …
SQL to create data as table when there is no relation in SQL
I have following tables Table 1 i have rates Table 2 i have Dates Table 3 i have data which show price of rates which are applicable between start and end i want to create table 4 with SQL query in which i have every rate (from table 1) + every date (from table 2) + if that date exist
Reading and Writing UTC to TIMESTAMP in Postgresql
I have a Java application that inserts data into a database using prepared statements. In the preparedStamement date is set in UTC format. I want to be sure that when read and write operations execute on the table, the response should ALWAYS be in UTC format. At the below query, when the data is read it will …
how can I can running status from my table?
I have a below table which has multiple rows with same executionid and different status. How can I get the row which status is running, rows will be exclude if executionid associated with both running and completed status? Below image is the sample data : Expected result should be: Answer Using String_AGG() t…
Oracle SQL: check amount of active users on given date (check closest date of grouped field)
Have a table given, holding the status history of a user: ID USERID MODIFIED STATUS 1 1 01.01.2020 inactive 2 1 01.07.2020 active 3 2 04.08.2020 active 4 2 04.06.2020 active 5 2 01.08.2020 inactive 6 2 01.10.2020 active 7 3 01.09.2020 inactive I want to provide a date, i.e. 01.07.2020, and understand how many…
Convert Raw SQL to Bookshelf/Knex
I’m trying to convert a simple raw SQL to use Bookshelf/Knex in JavaScript: Original SQL: select * from o where o.id = 1 or o.id = 2 and (o.is_a = true or o.is_b = true) and o.status = ‘good’; I’ve tried to rewrite it in multiple ways using .orWhere .andWhere but cannot get the same re…
How do I merge OUTER JOIN and UNION results?
I have two tables, and I want ALL the data from both. If the tables have a matching AssetID, then join them on one row. If not, then on separate rows. A full outer join sounds like the right approach but I have a problem in how to select the keys depending on which table it comes from. Goal: Produces