I have three tables x, y, z that have similar columns like this and the other columns are different: table x: id user_id month year total_price 1 1 January 2023 100.00 2 1 February 2023 200.00 table y: id user_id…
Please help with self join inside one table by different fields. given table "Items": Used PostgreSQL. I need to get all the values from such table, for which factId = 'urn:fact:drug:170' and factId1 ='urn:fact:drug:171' and factId1.value ='false' and arrayIndexes are…
I am looking to group users into separate groups based on when they bought something. A new group starts after it's been more than 90 days when the user last bought an item. Example of what I am looking for:…
I'm trying to customise related product of woocommerce First point is : the query is not updated for every requested page as I can imagine in term of server load. So how could i force to stock the custom result…
lets say i have a table like that id name address Doc 1 {1:mark,2:john} {1:Home,2:Work,3:Club} {NI:299,Pass:A159} 2 {1:Max,2:Mo} {1:Home} {NI:300011} what i want is to write a qurey to select a value from cell based on the key for example…
I need to find unique records from mysql table / dataframe. As per the business logic, if the time difference is less than 5 seconds (mobile number and code should be the same) then that record is considered as a…
I have this query I'm running in MySQL: SELECT count(*) FROM library AS l JOIN plays AS p ON p.user_id = l.user_id AND l.path = p.path WHERE l.user_id = 20977 AND p.time >= '2022-10-17'; When EXPLAIN ANALYZE is run: |…
I am looking for the right query to retrieve orders that are 3 calendar years old. Example: It is October 18, 2023, and therefore I want to collect all orders from January 1, 2021 until October 18, 2023. SELECT *…
I want to query a JSONB array using a regex filter. Following the docs, I should be able to do: WHERE jsonb_path_exists( data, '$.value[*] ? (@ like_regex $foo flag "i")', jsonb_build_object('foo', 'demo') ) But I'm getting an error: ERROR: syntax…
I'm trying to extract the following JSON message using SQL: { "document": { "Invoice": { "_NavRecordId": "Purch. Inv. Header: 06IF+230033", "InvoiceNumber": "06IF+230033", "PurchaseOrderNumber": "P0032259", "InvoiceLine": [ { "_NavRecordId": "Purch. Inv. Line: 06IF+230033,1", "item": "A05284", "Quantity": "2", "PurchaseOrderLineNumber": "1" }, {…