I don't know if I'm using the correct terminology, but I'm trying to find the cumulative sum of each node in a tree structure, but the children node only contribute a percentage of its cumulative sum to its parent node's…
Why this: with j(jval) AS ( select JSON_SERIALIZE('{"contract" :{"contract-type" : "P", "contract-id" : 123451, "proposal-no" : "123456", "serial-no" : "serialasd922", "product-code" : "s093", "contract-sign-dt" : "2016-12-01T00:00:00", "contract-start-dt" : "2016-12-09T00:00:00" } }' pretty) from dual ) SELECT json_transform(jval, insert '$.contract[*].x' =…
So I have 2 tables - Videos and Watches (with datetime). I want to order the videos by the last time watched by a given user, with the never before watched videos coming first and the rest being sorted by…
I am new into databases and PostgreSQL and today I wanted to import this dataset: https://www.kaggle.com/datasets/wilmerarltstrmberg/recipe-dataset-over-2m/data into a PostgreSQL so I can make a recipe finder app. I started by creating a new database, then creating a table and then…
I'm learning PostgreSQL and want to understand the exact order in which SQL statements are evaluated. After researching, I found the following evaluation order: 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. HAVING 7. SELECT 8.…
| Column A | character varying[] | ------------------------ | | {value1,value2,value3} | Tried with select * from some_table where columnA LIKE '%value1%' but it returns error. Then tried filtering expected value by using select columnA from some_table but now it…
I have a column called tags of type JSONB in my PostgreSQL table called EMPLOYEE. There is a use case where I want to allow users to query this JSONB column, i.e., tags. Users can give inputs like: tags.inoffice.present =…
+----------+------------+ | id | name | +----------+------------+ | 1 | storeone | | 2 | storetwo | | 3 | storethree| +----------+------------+ +----------+------------+------------+ | id | storeid | date | +----------+------------+------------+ | 1 | 1 | 2024-07-01 | | 2…
In general, I have a working query which is "Having A or B". It works, but I also want to add one more statement. I tried "Having (A and C) or (B and C)" -> gives no user_id, tried "Having…
I have a table that has users' activiies on their membership (activate or deactivate) and when those activities happened. The activity table look like this: user_id activity date 123 activate 06/01/2024 123 deactivate 06/15/2024 123 activate 06/20/2024 123 deactivate 06/30/2024…