Mysql – Sort and limit joined table
I have this majors table id name group score 1 Computer Science A 688 2 Electrical Engineering B 647 3 Psychology A 616 (more data on the link below) And this tests table id group score 1 B 690 2…
PostgreSQL multiplies columns when joining two tables
I have two tables similar to this example (simplified for clarity): parent: | id | name | amount | year | ---------------------------------------- | 101 | Henry | 300 | 2020 | ---------------------------------------- | 102 | Carol | 100 | 2020…
Mysql – Copy a table from one database to another database table
I have a MySQL database named wc_players in which it has a table called players_wc. I want to copy the entire table to another database named as fifa which already has a table named fifa_wc. How can i add this…
Mysql – Create Custom Column based on Account Type
I'm trying to use a SQL query to build a custom field that will identify each account as Search or PMax or Both (Search & PMax). Please help Thank you. Santosh. Here is rawdata and expected output in the attached…
Postgresql – Postgres check existence of any array element within JSONB column with another array
I am working on PostgreSQL 13.9. I want to check an array within a JSONB with another input array to see if there was any overlap of elements between the two then return true in the sample SQL below select…
Get records based on count greater than 2 in postgresql
I have a table named user_activity. user_id book_id event_type_id 1 1 1 1 1 1 1 1 1 1 2 1 1 2 1 1 2 1 1 3 1 event_type_id represents "User has viewed the book". I want to…
Postgresql – How can I pivot a table so that the rows become columns?
I have a table like this named people: first_name id total type Joe 1234 13 onsite Joe 1234 9 remote Jane 4321 99 remote Jane 4321 1 onsite I want to query it and have the results returned like this:…
Postgresql – Return multiple tables with different columns in Postgres
I have the following tables which i want to write a function to return data from Table Definitions create table device ( deviceid uuid not null constraint pk__device__49e123311461d246 primary key, serialnumber varchar(255), productcode varchar(255), description varchar(255), softwareversion varchar(255), build varchar(255),…
duckdb SQL select result to JSON
Is there a way to convert the result of a SELECT query on a table directly to JSON without writing it to a file? Perhaps with the JSON extension of duckdb? I could also use the python client, where I'd…
How can I calculate a user's daily streak in postgresql?
My goal I'm trying to track and display a user's daily streak posting to my app, but struggling to write a query that works reliably and returns an accurate count. Some context My app has a prompt and a post…