Postgresql – Max QTY with price <= 100 (Query)
Name Price Qty A 3 30 B 5 3 C 5 3 D 6 20 i have a table contain data like this. i want to get what is the maximum of sum(qty) that i can provide Price <= 100…
Postgresql – Workarounds for unique index based on parent table
I have a table structure analogous to this towns (id, name, region_id) regions (id, name, country_id) countries (id, name) I need to enforce the uniqueness of town name within the country. In a given country, if we put together all…
How to use a JSON date column in where condition in a sql query
I have data in this format below: first column is ID and second column is updated_at id updated_at 514 1664296267649490 747 1632984531588160 1341 1622023170903700 1889 1667852918885970 4604 1623874503631630 4769 1623874506587930 4813 1623874507218100 4881 1623874507579260 4922 1623874508093550 4992 1623874510601910 the updated_at…
How can I filter available time slots between booked appointments in PostgreSQL?
I am trying to implement an appointment manager for a dentist office. Thing is I am not sure how to properly implement such function. Right now I have managed to create these two tables that will handle it: CREATE TABLE…
Amazon web services – How to duplicate same row X number of times based on the value of a column?
I have a dataset looks like this: Link_number Houband Time Mean_speed Sample_Number Link 1 8 8:00 52 2 Link 1 8 8:30 55 5 Link 2 9 9:00 20 3 Link 2 9 9:30 40 4 I need to do…
SQL query nested arrays JSON Snowflake
I am trying to query a table in Snowflake that is built with a single field labeled "Value" and each row is a separate JSON object. Row 1: { "artist": { "artistID": "artist_1", "artistName": "Roblox" }, "descriptors": { "styles": […
Postgresql – Postgres select including data from previous rows
Given a postgres table containing the following items: tag_id, duplicate_tag_id, tag_created_at_timestamp 14175, 14178, ... 14175, 14177, ... 14176, null, ... 14177, 14178, ... 14178, 14179, ... 14179, null, ... 14180, null, ... 14181, null, ... Is it possible to write…
Mysql – How to show most common pairings of column values in SQL, specifically with an != statement
I'm working RWFD emergency room dataset, which includes patient's race and what department they were referred to. I'm trying to show which department each race (7 total) is most commonly referred to, with the stipulation that department_referral is not 'None'.…
Postgresql – How to optimise a recursive SQL query to find the cheapest path
I'm working on a recursive SQL query in PostgreSQL to find the minimum cost required to reach various cities from Montreal. The data follows a directed acyclic graph (DAG) structure, where each flight connection is represented in a Flights table…
Postgresql – Substracting two CASE Statements with 'AND' operator condition returns NULL when it should return a specific integer
I'm doing this StrataScratch SQL question. The suggested solution uses two subqueries for each max value of each department, and then it substracts them to get the absolute value. I wanted to solve this question using dense_rank/rank window function by…