I have a table with an id and the json column prod as the structure below { "1": { "product": "chain", "spare_part": [], "sp_done": 0 }, "2": { "product": "bike", "spare_part": { "1": {"part": "pedal", ...}, "2": {"part": "switch gear",…
I have a situation where I need to use DISTINCT to get unique values. But there is additional condition. In the duplicate rows I get some have a value SUCCESS and some have value FAILURE. I need it so that…
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…
I have a column of a table with this kind of structure { "Warehouse1": { "stock": 100, "batches":{...} }, "Warehouse32": { "stock": 90, "batches":{...} }, "Warehouse58": { "stock": 43, "batches":{...} }, "Warehouse99": { "stock": 55, "batches":{...} }, "Warehouse101": { "stock":…
I'm working on a BI request and I'm stuck and need your help :-) Here's the context : My company is receiving products and selling them. Until here, nothing complex :-) The selling price is varying depending on my available…
I have these three tables: "courses table" id course_title chapters_count 1 course 1 5 2 course 2 3 "chapters table" id chapter_title sub_chapters_count course_id --references courses(id) 1 chapter 1 2 1 2 chapter 2 4 1 3 chapter 3 1…
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…
I have a table: create table accounts_service.operation_history ( history_id bigint generated always as identity primary key, operation_id varchar(36) not null unique, operation_type varchar(30) not null, operation_time timestamp with time zone default now() not null, from_phone varchar(20), user_id varchar(21), -- and…
I'm working with PostgreSQL and using pg_export_snapshot() to create a snapshot in one transaction, which I then import into another transaction using SET TRANSACTION SNAPSHOT. I noticed that PostgreSQL requires the original (exporting) transaction to stay active while the snapshot…
I'm trying to add a timestamp column, updated_at, in the updates when doing a upsert using on_conflict_do_update. Below is my current implementation but encounter the below error when running at the last line. Exception has occurred: ProgrammingError (psycopg2.errors.AmbiguousColumn) column reference…