Advertisements Given a string column col1 containing hierarchical relationships, such as: id1/id3/id4/id5 id2/id3/id6/id4/id10 id1/id7/id8 id10/id3/id14/id21/id34 how would you get the unique numbers of ids under id3? In this case it’s 7: [id4, id5, id6, id10, id14, id21, id34] I have a sketch of a pretty complex solution using arrays by splitting the col1, reversing the… Read More Counting Chains of Strings
Advertisements I am trying to write a query that will spit out the subject_key’s that only appear a single time in the public.message table. What I have below is spitting out keys for people who have more than 1 message so I am missing something but am unsure what I am doing wrong. with cte… Read More Get Values That Only Appear Once
Advertisements The database has a Salary table in which each employee has an ID, as well as a column for the department ID. Write an SQL query to find the employees who receive the three highest salaries in each department. Salary: Id Name Salary DepartmentId +—-+——-+——–+—- | 1 | Petr | 85000 | 1 |… Read More I want to understand how to make another sql query
Advertisements I have the following table : entity_id time_c next_time_c 1 ‘2023-01-02 10:34:36’ ‘2023-01-10 15:12:24’ 2 ‘2023-03-01 16:10:12’ ‘2023-03-20 22:47:59’ My goal is to calculate the interval between time_c and next_time_c without Saturday and Sunday and the full interval. I come up with the following query : WITH parms (entity_id, start_date, end_date) AS ( SELECT… Read More Calculate several timedifference give different output
Advertisements I am trying to write a query for select a specific year data from data table, but I am getting an error. I am trying to reach the average of the hourly fee for cars parked in 2022. I wrote this query, but I am getting an error. My query: (Not working) SELECT AVG(HourlyFee)… Read More Selecting specific year data from date table (PostgreSQL)
Advertisements I am trying to get only date without time in postgres from the following statement: select current_date – date_trunc(‘day’,interval ‘1 month’); But returns me that: 2023-02-07 00:00:00 What could be going wrong here. I see that date_trunc function returns timestamp and intervals cannot be cast to date type: select current_date – interval ‘1 month’::date;… Read More extract only date from current_date + interval data type in postgres
Advertisements Suppose the following: create table person( id serial primary key, person_name varchar(55) ); create table category( id serial primary key, category_name varchar(55) ); create table category_subscription( id serial primary key, category_id bigint references category(id), person_id bigint references person(id) ); insert into person values(1, ‘george’); insert into category values(10, ‘homework’), (20, ‘promotion’), (30, ‘delivery’); insert… Read More How to cast "truthy" and "falsey" strings to boolean in Postgres?
Advertisements In my app I have users and tags. Tags have a name property (tag.name). I want to enforce unique tag names per user. Meaning a user cannot have two matching strings in the name column. Tag name uniqueness is only account specific. Meaning user 1 and user 2 can both have a tag.name equal… Read More Postgres Column Value Unique to User
Advertisements here’s a sample from my table in Metabase PostgreSQL: timestamp 2023-01-25T05:00:01+03:00 2023-01-25T05:01:01+03:00 2023-01-25T05:05:01+03:00 2023-01-25T05:13:01+03:00 2023-01-25T05:14:01+03:00 2023-01-25T05:20:01+03:00 2023-01-25T05:24:01+03:00 2023-01-25T05:29:01+03:00 I want to group by 15-minute interval and count events that happen in the interval: timestamp count 2023-01-25T05:00 5 2023-01-25T05:15 3 this didn’t work SELECT date_trunc(‘hour’, timestamp_column) + (floor(date_part(‘minute’, timestamp_column) / 15) * interval ’15 minute’)… Read More group by 15-minute interval in PostgreSQL
Advertisements How to optimize requests? Hello there is a chat which consists of the following tables Chat rooms id, type(USER_TO_USER, USER_TO_SHOP, USER_TO_PLATFORM), name Chat Room Members room_participants id, participantType (USER, SHOP, PLATFORM), participant_id list of messages room_messages – id, participant_id, text, is_read Additionally: users table id, first_name, last_name, image, image_src shops table id, name, logo,… Read More How to optimize sql requests?