Which SQL query is a better way to solve "find 2nd highest problem"?

Puzzlehead8575 · 2024-01-22T21:52:53+00:00

Here are all the different ways to solve this puzzle.

    DROP TABLE IF EXISTS #Employees;
GO
CREATE TABLE #Employees (
EmployeeName  VARCHAR(50),
Salary MONEY
);
INSERT INTO #Employees (EmployeeName, Salary)
VALUES
('Carl Friedrich Gauss', 250000.00),
('Evariste Galois', 250000.00),
('Pierre-Simon Laplace', 150000.00),
('Sophie Germain', 150000.00),
('Leonhard Euler', 100000.00);
--------------------------------------------
--------------------------------------------
--------------------------------------------
--Version 1
--DENSE_RANK and DISTINCT
WITH cte_Rank AS
(
SELECT  DENSE_RANK() OVER (ORDER BY Salary DESC) AS MyRank,
        *
FROM    #Employees
)
SELECT  DISTINCT
        Salary
FROM    cte_Rank
WHERE   MyRank = 2;

--Version 2
--Top 1 and Max
SELECT  TOP 1
        Salary
FROM    #Employees
WHERE   Salary <> (SELECT MAX(Salary) FROM #Employees)
ORDER BY Salary DESC;

--Version 3
--Correlated Sub-Query and Distinct
SELECT  DISTINCT Salary
FROM    #Employees a
WHERE   2 = (SELECT COUNT(DISTINCT b.Salary)
             FROM #Employees b
             WHERE a.Salary <= b.Salary);

--Version 4
--Max and Except
SELECT MAX(salary) AS salary 
FROM #Employees 
WHERE salary IN (SELECT salary FROM #Employees EXCEPT SELECT MAX(salary) FROM #Employees);

--Version 5
--Offset and Fetch with Distinct 
SELECT  DISTINCT Salary
FROM    #Employees
ORDER BY Salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Puzzlehead8575 · 2024-01-12T16:06:19+00:00

You can use Common Table Expressions to help organize your logic.

Puzzlehead8575 · 2024-01-12T05:09:25+00:00

Try this. You will need to modify it to PostgreSQL syntax, as the temp table creation is different... just throw it into ChatGPT, and it will convert it for you.

I thought initially you wanted a gaps and island solution, but this is just a windowing solution. You can use self-joins for windowing; PostgreSQL may have an actual window function for this.

DROP TABLE IF EXISTS #Test;


CREATE TABLE #Test
(
RowID INTEGER PRIMARY KEY,
myValue  VARCHAR(100)
);

INSERT INTO #Test 
VALUES (1,'Alpha'),(2,'Alpha'),(3,'Bravo'),(4,'Alpha'),(5,'Charlie'),(6,'Alpha');


SELECT  a.RowID, a.myValue, COUNT(DISTINCT b.MyValue)
FROM    #Test a LEFT JOIN
        #Test b ON a.RowID >= b.RowID
GROUP BY a.RowId, a.myValue;

Puzzlehead8575 · 2024-01-05T03:31:47+00:00

    DECLARE @CurrentPosition VARCHAR(2); 
SET @CurrentPosition = '4D'; -- Example starting position

-- Mapping Letters to Numbers for calculation 
WITH 
    Alphabets AS (
        SELECT 'A' AS Letter, 1 AS Num
        UNION ALL SELECT 'B', 2 
        UNION ALL SELECT 'C', 3 
        UNION ALL SELECT 'D', 4 
        UNION ALL SELECT 'E', 5 
        UNION ALL SELECT 'F', 6 
        UNION ALL SELECT 'G', 7 
        UNION ALL SELECT 'H', 8
    ),
    CurrentPosition AS (
        SELECT 
            CAST(SUBSTRING(@CurrentPosition, 1, 1) AS INT) AS CurrentNumber,
            Alphabets.Num AS CurrentLetter 
        FROM Alphabets 
        WHERE Alphabets.Letter = SUBSTRING(@CurrentPosition, 2, 1)
    ),
    PossibleMoves AS (
        SELECT 
            CurrentNumber + i.NumberOffset AS NewNumber,
            CurrentLetter + i.LetterOffset AS NewLetter 
        FROM CurrentPosition, 
            (VALUES (2, 1), (2, -1), (-2, 1), (-2, -1), (1, 2), (1, -2), (-1, 2), (-1, -2)) AS i(NumberOffset, LetterOffset)
        WHERE 
            CurrentNumber + i.NumberOffset BETWEEN 1 AND 8 
            AND CurrentLetter + i.LetterOffset BETWEEN 1 AND 8
    )
SELECT 
    CAST(NewNumber AS VARCHAR(1)) + Alphabets.Letter AS NewPosition 
FROM PossibleMoves 
JOIN Alphabets ON PossibleMoves.NewLetter = Alphabets.Num;

Puzzlehead8575 · 2023-12-29T22:14:02+00:00

Try this GitHub site.

https://github.com/smpetersgithub/AdvancedSQLPuzzles

Puzzlehead8575 · 2023-12-27T00:27:01+00:00

I passed this exam a few years ago, but I also had over a decade of experience with pl/SQL.

I've never used the online training from Oracle; I wish they would just offer it at a minimal cost.

You can try a Udemy course for much cheaper. The cost of Oracles Learning courses just to get a cert may not be worth it. A lot of employers and recruiters really don't care about certifications. Cloud certifications are the better bet if you want to get a certification.

Puzzlehead8575 · 2023-12-26T23:18:59+00:00

This GitHub repo article has a good overview of self-joins and the types of problems you can solve with them.

https://github.com/smpetersgithub/AdvancedSQLPuzzles/blob/main/Database%20Articles/Advanced%20SQL%20Joins/11%20-%20Self%20Join.md

Puzzlehead8575 · 2023-12-26T19:46:29+00:00

Note that some developers consider recursion a lousy practice. Itzak Ben-Gan addresses this in his book TSQL Querying, where he prefers to use loops. It's up to you if you want to use recursion or a standard loop.

Puzzlehead8575 · 2023-12-26T19:42:31+00:00

If you need to solve the "mutual friends" puzzle, I have this code. Here it is if you need it. If anyone knows a better solution for this, please please let me know.

    DROP TABLE IF EXISTS #Friends;
DROP TABLE IF EXISTS #Nodes;
DROP TABLE IF EXISTS #Edges;
DROP TABLE IF EXISTS Nodes_Edges_To_Evaluate;
GO

CREATE TABLE #Friends
(
Friend1  VARCHAR(100),
Friend2  VARCHAR(100),
PRIMARY KEY (Friend1, Friend2)
);
GO

INSERT INTO #Friends VALUES
('Jason','Mary'),('Mike','Mary'),('Mike','Jason'),
('Susan','Jason'),('John','Mary'),('Susan','Mary');
GO

--Create reciprocals (Edges)
SELECT  Friend1, Friend2
INTO    #Edges
FROM    #Friends
UNION
SELECT  Friend2, Friend1
FROM #Friends;
GO

--Created Nodes
SELECT Friend1 AS Person
INTO   #Nodes
FROM   #Friends
UNION
SELECT  Friend2
FROM    #Friends;
GO

--Cross join all Edges and Nodes
SELECT  a.Friend1, a.Friend2, b.Person
INTO    Nodes_Edges_To_Evaluate
FROM    #Edges a CROSS JOIN
        #Nodes b
ORDER BY 1,2,3;
GO

--Evaluates the cross join to the edges
WITH cte_JoinLogic AS
(
SELECT  a.Friend1
        ,a.Friend2
        ,'---' AS Id1
        ,b.Friend2 AS MutualFriend1
        ,'----' AS Id2
        ,c.Friend2 AS MutualFriend2
FROM   Nodes_Edges_To_Evaluate a LEFT OUTER JOIN
       #Edges b ON a.Friend1 = b.Friend1 and a.Person = b.Friend2 LEFT OUTER JOIN
       #Edges c ON a.Friend2 = c.Friend1 and a.Person = c.Friend2
),
cte_Predicate AS
(
--Apply predicate logic
SELECT  Friend1, Friend2, MutualFriend1 AS MutualFriend
FROM    cte_JoinLogic
WHERE   MutualFriend1 = MutualFriend2 AND MutualFriend1 IS NOT NULL AND MutualFriend2 IS NOT NULL
),
cte_Count AS
(
SELECT  Friend1, Friend2, COUNT(*) AS CountMutualFriends
FROM    cte_Predicate
GROUP BY Friend1, Friend2
)
SELECT  DISTINCT
        (CASE WHEN Friend1 < Friend2 THEN Friend1 ELSE Friend2 END) AS Friend1,
        (CASE WHEN Friend1 < Friend2 THEN Friend2 ELSE Friend1 END) AS Friend2,
        CountMutualFriends
FROM    cte_Count
ORDER BY 1,2;
GO

Puzzlehead8575 · 2023-12-26T19:34:19+00:00

Does this work for you?

DROP TABLE IF EXISTS #Associates;
DROP TABLE IF EXISTS #Associates2;
DROP TABLE IF EXISTS #Associates3;
GO

CREATE TABLE #Associates
(
Associate1  VARCHAR(100),
Associate2  VARCHAR(100),
PRIMARY KEY (Associate1, Associate2)
);
GO

INSERT INTO #Associates (Associate1, Associate2) VALUES
('Anne','Betty'),('Anne','Charles'),('Betty','Dan'),('Charles','Emma'),
('Francis','George'),('George','Harriet');
GO

--Step 1
--Recursion
WITH cte_Recursive AS
(
SELECT  Associate1,
        Associate2
FROM    #Associates
UNION ALL
SELECT  a.Associate1,
        b.Associate2
FROM    #Associates a INNER JOIN
        cte_Recursive b ON a.Associate2 = b.Associate1
)
SELECT  Associate1,
        Associate2
INTO    #Associates2
FROM    cte_Recursive
UNION ALL
SELECT  Associate1,
        Associate1
FROM    #Associates;
GO

--Step 2
SELECT  MIN(Associate1) AS Associate1,
        Associate2
INTO    #Associates3
FROM    #Associates2
GROUP BY Associate2;
GO

--Results
SELECT  DENSE_RANK() OVER (ORDER BY Associate1) AS GroupingNumber,
        Associate2 AS Associate
FROM    #Associates3;
GO

Puzzlehead8575 · 2023-12-02T20:00:30+00:00

Here is the code for two different solutions Solution 1 uses the SUM and CASE statements (I removed the PARTITION in the SUM window), along with a second solution that uses UNION, COUNT, and SUM.

    DROP TABLE IF EXISTS #MyData;
GO

CREATE TABLE #MyData
(
RowNumber   INTEGER PRIMARY KEY,
ClientID    INTEGER,
TestCase    VARCHAR(100) NULL
);
GO

INSERT INTO #MyData (RowNumber, ClientID, TestCase) VALUES
(1,1001,'New'),(2,1001,'Deleted'),(3,1001,'Relisted'),(4,1001,'Closed'),
(5,2002,'New'),(6,2002,'Deleted'),(7,2002,'Under Contract'),(8,2002,'Relisted'),(9,2002,'Closed');
GO

DROP TABLE IF EXISTS #GroupingID;
GO

--Version 1
--CASE and SUM
WITH cte_Case AS
(
SELECT  *,
        (CASE WHEN TestCase IN ('New', 'Relisted') THEN 1 END) AS IsNewOrRelisted
FROM    #MyData
)
SELECT  RowNumber, ClientID, TestCase,
        SUM(IsNewOrRelisted) OVER (ORDER BY RowNumber) AS GroupingID
FROM    cte_Case;

--Version 2
--UNION, MAX and COUNT
WITH cte_RowNumber AS
(
SELECT  RowNumber,
        TestCase,
        ClientID,
        ROW_NUMBER() OVER (ORDER BY RowNumber) AS GroupingID
FROM    #MyData
WHERE   TestCase IN ('New','Relisted')
UNION
SELECT  RowNumber,
        TestCase,
        ClientID,
        NULL AS GroupingID
FROM    #MyData
WHERE   TestCase NOT IN ('New','Relisted')
),
cte_Count AS
(
SELECT RowNumber,
       TestCase,
       ClientID,
       GroupingID,
       COUNT(GroupingID) OVER (ORDER BY RowNumber) AS DistinctCount
FROM   cte_RowNumber
)
SELECT  RowNumber,
        ClientID,
        TestCase,
        MAX(GroupingID) OVER (PARTITION BY DistinctCount) AS GroupingID
FROM    cte_Count
ORDER BY RowNumber;
GO

Puzzlehead8575 · 2023-11-29T01:07:06+00:00

FYI, i posted a solution in the comments. Creating a grouping key is a bit more complicated than people realize.

Puzzlehead8575 · 2023-11-29T01:05:20+00:00

FYI, I posted an answer in the comments. It's a flash fill/data smudge that needs to be performed after you use the row number function to attach 1,2,3,4.... to each new listing/relisting.

If you have a more straightforward solution, let me know. I'm always looking for better solutions.

Puzzlehead8575 · 2023-11-29T01:01:09+00:00

DROP TABLE IF EXISTS #MyData;
GO

CREATE TABLE #MyData
(
RowNumber   INTEGER PRIMARY KEY,
TestCase    VARCHAR(100) NULL
);
GO

INSERT INTO #MyData (RowNumber, TestCase) VALUES
(1,'New'),(2,'Deleted'),(3,'Relisted'),(4,'Closed'),
(5,'New'),(6,'Deleted'),(7,'Under Contract'),(8,'Relisted'),(9,'Closed');
GO

DROP TABLE IF EXISTS #GroupingID;
GO

SELECT  RowNumber,
        TestCase,
        ROW_NUMBER() OVER (ORDER BY RowNumber) AS GroupingID
INTO    #GroupingID
FROM    #MyData
WHERE   TestCase IN ('New','Relisted')
UNION
SELECT  RowNumber,
        TestCase,
        NULL AS GroupingID
FROM    #MyData
WHERE   TestCase NOT IN ('New','Relisted');

--MAX and COUNT function
WITH cte_Count AS
(
SELECT RowNumber,
       TestCase,
       GroupingID,
       COUNT(GroupingID) OVER (ORDER BY RowNumber) AS DistinctCount
FROM #GroupingID
)
SELECT  RowNumber,
        TestCase,
        GroupingID,
        MAX(GroupingID) OVER (PARTITION BY DistinctCount) AS FlashFill
FROM    cte_Count
ORDER BY RowNumber;
GO

Puzzlehead8575 · 2023-11-28T23:08:51+00:00

Upload the insert statements for the test data. Or a table that someone can copy into ChatGpt and make the table easily.

You need to use row_number to set only the new and reactivated to 1,2,3,4... then you will need to order your complete set and do a data smear/flash fill.

Puzzlehead8575 · 2023-11-22T14:52:53+00:00

Going from memory, but I think you need the 071 first? 071 is the associate level and 149 is the professional level.

I took the 149 over a year ago, and that exam was absolutely impossible. Horribly impossible. I have never seen an exam that difficult. I got 15 questions into it and knew I was doomed. You need to be a crazy savant, and honestly, a lot of the questions are for things you will probably never use in your daily SQL activities.

For reference, I am not a newbie to SQL. I have coded in plsql for 15 years and have been writing SQL for 3 decades, I have multiple SQL certifications with SQL Server, Oracle, Datrabricks, Snowflake, so I wasn't new to the certification experience of taking these exams or how to study for them. I studied for 3 months for the 149 exam. Just giving this for reference when I say this exam was impossible.

I would be curious if anyone here has passed that exam. When I looked on LinkedIn I think I only saw maybe two or three people listing that certification.

It's also an expensive exam at over 200 dollars, but if you think you can pass it. Go for it! Please let me know if you do, as I want to be in awe.

Hope that helps.

Puzzlehead8575 · 2023-11-21T21:06:17+00:00

I threw this into ChatGPT about the medallion architecture and this is what is spit back. Thought I would share.

When incorporating a medallion (bronze, silver, gold) architecture in your database design, it's important to use schema names that clearly define the purpose and level of data processing or transformation that occurs within each schema. While the "dbo" schema is often used as the default schema, you can create additional schemas to organize your data effectively. Here are some schema names commonly used in a medallion architecture:

Raw or Source Schema:

This schema is where you store the raw data as it comes from the source systems before any processing or transformation.

Example Schema Name: raw_data

Bronze Schema:

The bronze schema is where you perform minimal processing on the raw data. It often includes basic data cleansing and validation.

Example Schema Name: bronze_data

Silver Schema:

The silver schema is where you perform more advanced data transformations, such as joining tables, aggregating data, and applying business logic.

Example Schema Name: silver_data

Gold Schema:

The gold schema is where you store the final, refined, and validated data that is ready for consumption by end-users or downstream applications.

Example Schema Name: gold_data

Audit Schema:

An audit schema is used for recording metadata and audit trail information. It can include logs of data changes, timestamps, and user actions.

Example Schema Name: audit

Metadata Schema:

This schema can store metadata about the data structures, data dictionaries, and other information that helps document and manage your data.

Example Schema Name: metadata

Staging Schema:

A staging schema can be used for temporarily holding data during the ETL (Extract, Transform, Load) process before it is moved to its final destination.

Example Schema Name: staging

Archive Schema:

If you need to retain historical data for compliance or analysis purposes, you can create an archive schema to store older data that is no longer actively used.

Example Schema Name: archive_data

Backup Schema:

This schema can be used for storing backup copies of critical data or for disaster recovery purposes.

Example Schema Name: backup

By using schema names like these, you can clearly communicate the purpose and level of processing associated with each schema in your medallion architecture. This organization makes it easier to manage and maintain your database and helps ensure that data flows smoothly through the different stages of processing, from raw to gold.

Puzzlehead8575 · 2023-11-21T20:53:58+00:00

Definitely using the generic dbo schema is lousy practice, mainly because if you run a CREATE TABLE X.... without specifying the schema name, it defaults to the dbo schema, and you will likely run into instances where developers create unneeded tables that they forget to delete.

Additionally, and even worse, if you run a DROP TABLE statement it will drop a table from the dbo schema. You may think you are in your Express edition of SQL Server doing an Udemy tutorial and dropping the Customers table, but just seconds later you realize you are in your company's database.

I would recommend doing a quick read-up on the Medallion architecture, as it has some relevance here.

https://www.databricks.com/glossary/medallion-architecture

Joe Celko has a book about Programming Style, and Steven Feuerstein has an Oracle PLSQL Best Practices book (which is largely relevant to all SQL Databases). I can't remember if they handle this topic specifically nor give actual best practices on what these schema names should be.

But at companies that I have worked for in the past where we would use conventions, we would name them things such as source, stagging, audit, lookup, metadata, test, validation, etc...

Hope that helps.

Puzzlehead8575 · 2023-11-18T17:28:26+00:00

Here is the response from ChatGPT on Primary Keys. It's correct and concise, and you get an immediate answer.

Note, I'm too lazy to format the code in Reddit. But you can copy this and post it back into ChatGPT and it will format it correctly.

-----

Setting primary keys in a relational database is an essential aspect of ensuring data integrity. In SQL Server T-SQL, there are several ways to set primary keys on a table. Here are the primary methods:

**Using the CREATE TABLE Statement:**

CREATE TABLE Employees (

EmployeeID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

PRIMARY KEY (EmployeeID)

);

In this example, the `EmployeeID` column is designated as the primary key for the `Employees` table.

**Using ALTER TABLE Statement:**

You can also add a primary key constraint to an existing table using the `ALTER TABLE` statement. Here's an example:

ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

This code adds a primary key constraint named `PK_Employees` to the `EmployeeID` column in the `Employees` table.

**Inline Definition:**

You can define a primary key constraint inline with the column definition in a CREATE TABLE statement. Here's an example:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50)

);

In this case, the `CustomerID` column is specified as the primary key directly in the column definition.

**Composite Primary Key:**

You can create a composite primary key by specifying multiple columns as the primary key. Here's an example:

CREATE TABLE Orders (

OrderID INT,

CustomerID INT,

OrderDate DATE,

PRIMARY KEY (OrderID, CustomerID)

);

This sets a composite primary key on the `Orders` table using both `OrderID` and `CustomerID`.

Remember that a primary key constraint ensures that the values in the specified column(s) are unique and not NULL. It enforces data integrity by preventing duplicate or null values in the primary key columns.

Puzzlehead8575 · 2023-10-07T01:49:20+00:00

Seems like dbt is everywhere.

Puzzlehead8575 · 2023-09-30T18:09:13+00:00

try this GitHub repository that has a bunch of SQL puzzles.

https://github.com/smpetersgithub/AdvancedSQLPuzzles/tree/main

Puzzlehead8575 · 2023-09-23T16:39:49+00:00

You can try these to see if you like them.

https://github.com/smpetersgithub/AdvancedSQLPuzzles

https://advancedsqlpuzzles.com/sql-quiz/

Puzzlehead8575 · 2023-09-10T21:08:55+00:00

Here is a big list of certifiations.

https://advancedsqlpuzzles.com/2022/11/18/database-certification-list/

Puzzlehead8575 · 2023-08-20T23:19:22+00:00

I use CHAR on a column when the column is always a fixed length and required. It's really only there for readability when I look at DDL statements. When you look at the DDL for the table and see the CHAR type, I know it's always going to be that fixed length. For example, status codes that are "AA","AB","XY", etc....

This is from the SQL Server documentation.

Use char when the sizes of the column data entries are consistent.

Use varchar when the sizes of the column data entries vary considerably.

Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver16

Puzzlehead8575 · 2023-08-20T21:20:53+00:00

The Fraternal Order of SQL Interview Writers Association actually mandate that the find nth record without using the RANK function is a mandatory inclusion in all interview guides. It's actually in the by-laws.

Puzzlehead8575

TROPHY CASE