I have a table, Account, which I am querying to identify old accounts based on sales records. An account may have multiple rows in a Property table. A property may be closed. If all properties on an account are closed,…
Supposed I have data as below: id cnt insert_time 1 234 1679131273 2 12 1679131273 3 80 1679131514 4 30 1679131514 5 124 1679131605 6 4.3 1679131605 7 4 1679131935 8 1.5 1679131935 9 5 1679292635 10 56 1679292635 I…
I used this MySQL table. I am trying to find the percentage share of each language over the course of 30 days from it. select language as languages, round(100*count(*)/total, 2) from job_data cross join (select count(*) as total from job_data)…
I understand that this query will produce numbers and cumulative tallies: SELECT number , SUM(number) OVER (ORDER BY number) cumulative_number FROM (SELECT 1 number UNION SELECT 2 UNION SELECT 3) inside GROUP BY number Result: number cumulative_number 1 1 2…
I am trying to find all the rows in my table, which a certain column is duplicated in them. and identify them by the unique id column that each row has. I want to delete them and leave only one…
I have two tables with a one-to-many relationship that looks something like this: CREATE TABLE products ( id UUID PRIMARY KEY, name TEXT ); CREATE TABLE product_variations ( id UUID PRIMARY KEY, product_id UUID REFERENCES products, attribute1 TEXT, attribute2 TEXT,…
Still learning SQL/PostgreSQL. I am struggling to figure out how to stop returning an array with a null value as [null] and instead just return an empty array. COALESCE( json_agg( CASE WHEN socials.id IS NULL THEN NULL ELSE json_build_object('id', socials.id,…
I have two tables that have a parent/child relationship, where one record in the parent table may correspond to N records in the child. Both tables have an amount column that I want to aggregate in one query, so I…
I have a DB function, say "myWrapperFunction"(), which returns a JSON array of objects as below: select * from "myWrapperFunction"() >> [{"a":"b","c":"d"},{"e":"f"}] I have a table "table1" with column 'column1'. I need to pass the values from table1.column1 into myFunction()…
I have a query that selects the rows from joined table as an array using ARRAY_AGG() function. select entity_number, ARRAY_AGG('{"property_id":"'||property_id||'","value":"'||value||'"}') entity_properties from entities join entity_properties on entities.id = entity_properties.entity_id where entities.id in ( select entity_id from entity_properties where value =…