Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
In the following scenario, there are 3 tables in a PostgreSQL database (tables and values are simplified for clarity). I am trying to get the following values: sum of the amounts for records (101, 102, 103) in the parent table…
lets get to the situation quickly, so i have a table which contains values as id f_name l_name phone 1 rohan mehra 9090909090 2 yusuf yohana 1010101010 3 rohan mehra 9090909090 4 yusuf yohana 1010101010 so if yu observe, there…
I have 3 tables : accounts, contacts, contact_activity. Accounts can have multiple contacts, also multiple activities whereas contacts can also have multiple activities. adding tables queries below: CREATE TABLE `accounts` ( `id` int(10) UNSIGNED NOT NULL, `companyName` varchar(255) COLLATE utf8mb4_unicode_ci…
I have two queries that I’m trying to combine, but I’m having an issue and I believe it has to do with the GROUP BY Date when I do LEFT JOIN. The date formats are the same and the only…
I have two tables similar to this example (simplified for clarity): parent: | id | name | amount | year | ---------------------------------------- | 101 | Henry | 300 | 2020 | ---------------------------------------- | 102 | Carol | 100 | 2020…
I have my models configured as below: public class Order { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public string UserId { get; set; } = null!; public DkUser User { get; set; } = null!; public int…
I have a SQL query which works but is slow. I wondered if there is a more efficient way to express it using joins. Scenario: Tables: productions, scripts and a join table, productions_scripts productions id (int) production (text) scripts id…
I am encountering an error while attempting to use the Postgres FDW extension to import a foreign schema from the same database server for performing join queries. Below, I have provided a snapshot of my pgAdmin for better clarity, along…
I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…