This might be difficult to explain. But Im trying to write a redshift sql query where I have want the count of organizations that fall into different market buckets. There are 50 markets. For example company x can be only…
Scenario: Invoice has many purchase_orders and purchase_orders has many invoices. Intermediate table is npayment_links which has foreign key invoice_id, purchase_order_id. Tech Stack Rails 5.x, Postgresql Here is my sample data invoices id name status 100 sample.pdf archived 101 sample1.pdf archived…
Im trying to add ROW_NUMBER() in a select query on top of a calculated field and get unique rownum for unique calculated field. But Im getting sequuential rownum for all the values in calculated field. How can I achieve it…
Having a table such as follows: CREATE TABLE Associations ( obj_id int unsigned NOT NULL, attr_id int unsigned NOT NULL, assignment Double NOT NULL PRIMARY KEY (`obj_id`, `attr_id`), ); this should occupy 16 bytes per row. So the overhead per…
How do you run an "order by" and then "replace" string, but keep the order? Context - I need the string "column_name" to be in the first line, hence using "zzz_column_name" to force it in the order by. And then…
My tables: questions id: serial content: text tags id: serial name: text questions_tags question_id integer tag_id integer I need to select all questions with tag 'css', but don't remove all other tags from JSON from jsonb_agg(). I have this SQL…
I have this query to fetch most recent conversations grouped by phone number, however the query time is too slow. How can it be faster? SELECT * from messages WHERE id IN (SELECT max(id) from messages GROUP BY phone) AND…
I have three tables: Customer, CustomerOrder, and OrderStatus. My database is filled with the following info: Customer id name 1 Bob 2 James CustomerOrder id customer amount status 1 1 100 1 2 1 83 1 3 1 432 2…
I have the following two tables: CREATE TABLE segments(session_id INT, segment_id INT, length DOUBLE PRECISION, movement BOOLEAN); INSERT INTO segments (session_id, segment_id, length, movement) VALUES (49,0,0,'f'),(49,1,180851,'t'),(49,2,1.31082,'f'),(49,3,1580.41,'t'), (49,4,1.0059,'f'),(49,5,4.47451,'t'),(49,6,1.69973,'f'),(55,0,2.76848,'f'), (55,1,2.60235,'t'),(55,2,3.807,'f'),(55,3,2.78887,'t'),(55,4,8.47029,'f'); CREATE TABLE trips (trip_id INT, session_ids INT[], distance DOUBLE PRECISION); INSERT INTO…
I have a query like this: select case when sp.provider='prv1' and sp.status_code = 404 then 'failed' when sp.provider='prv1' and sp.status_code = 200 then 'success' when time_taken is null or sp.status_code in (503) or sp.status_code is null then 'unavailable' else 'other'…