I have table, Table Book id,name,buyers And each row has the list of buyers. buyers are about 100000. So now I have two ideas. First one is Push the array of 100000 elements in row id, name, buyers 1, nice…
The goal is to find passengers in a flight database who accumulated 20,000 or more miles. I am asked to find the 10th passenger with the highest total miles. First I needed to create the top 10 list using the…
Let's consider the database table for currency conversion looks as below: fromCurrency toCurrency effective date conversion rate USD INR 1-Mar-2024 80 USD INR 1-Jan-2024 85 USD GBP 1-Oct-2023 .80 USD GBP 1-Mar-2024 .85 USD AUD 1-Mar-2023 1.55 USD AUD 1-Mar-2024…
I have the next query, what returns me categories with an array of games that assignment to them. How can I limit number of games in that array? SELECT categories.*, COALESCE(jsonb_agg(g.*) FILTER (WHERE g.name IS NOT NULL), '[]') AS games…
I have the following query: select count(*) as aggregate from users where date(created_at) < '2024-04-01'; It returns 2337355 which is a correct number. But it takes over 1.5 secs to be executed. Any idea how can I make it more…
I have a SQL query: select max(date) from index_constituents where (opening_closing ='O' and index_code ='buk350n' and issuer = 'cboe') and date = '2024-04-25'; id date issuer index_code opening_closing 1393 2024-04-25 cboe buk350n O 1394 2024-04-25 cboe buk350n O When I…
I have a table with a JSON column. The JSON column holds for each row, a JSON array with JSON objects such as this [{"my_value": 42, "category": "A"}, {"my_value": 100, "category": "C"}, {"my_value": 15, "category": "B"}, {"my_value": 123, "category": "D"}]…
I have table account create table account ( user_id int primary key, acc int, amt int ); with the next data user_id | acc | amt ---------+------+----- 727 | 2620 | 10 777 | 2620 | 100 and I want…
I have a function & I'm checking whether the table and column exist or not. If exits, then I need to alter the table. delimiter $$ create function column_exists(ptable varchar(300), pcolumn varchar(100)) returns CHAR reads sql data begin declare result…
I have a table like this: id date capacity depot reserved order_id 1 2024-04-29 69 1 0 0 2 2024-04-29 49 1 0 0 3 2024-04-29 69 1 0 0 4 2024-04-29 69 1 0 0 Now I want to…