I have 3 tables I am trying to query, with one table that links the other two. The tables are CITY, SITE, and ACTIVITIES. They contain sites within cities, and then separate activities at each site. Not every site has…
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…