160 ) Find the 4th Highest Salary

 

1) Find the 4th Highest Salary 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Question: Write a query to find the 4th highest salary from an Employee table.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query:

1 ) row_number( ) : will give all sequential numbers for the  ranks even if there is a duplicate rank 

  --  Next  rank = next sequential rank as per soted order 

 

2 ) Rank( ) : if duplicate rank then 

--- Next rank =  ( total previous  dup. ranks ) + prev rank + 1 ) 

 

3 ) Dense rank ( )  :  if duplicate rank then 

----Next rank = (previous rank / duplicate rank )  + 1 

------------------------------------------------------------------------------------------------------------------

 -------To create temp table for Rank based on Salaries ----------------------

------------------------------------------------------------------------------------------------------------------

WITH  Temp_Salary AS (

    SELECT     EmployeeID,      Salary,

       DENSE_RANK() OVER (ORDER BY Salary DESC) as SalaryRank

    FROM Employees

)

------- ** To show emplds with 4th highest salary ----------------------

SELECT  empid FROM Temp_Salary 

WHERE SalaryRank = 4; 

159 ) ) Query to find duplicate rows of loan id but time is different

 14 )  Query to find duplicate rows of loan id but time is different 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Due to a bug in an upstream system, a source file sent duplicate records for the same loan account number on the same day. 

Each duplicate row has a different SnapshotTimestamp. 

How would you write a query to select only the most recent row for each loan account and filter out the older duplicates?

-----

 use temp_db;


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ==========================================
-- 1. DDL: Create the table
-- ==========================================
CREATE TABLE loan_snapshots (
    loan_account_number VARCHAR(50) NOT NULL,
    snapshot_timestamp TIMESTAMP NOT NULL,
    loan_amount DECIMAL(12, 2),
    status VARCHAR(20)
);


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


-- ==========================================
-- 2. DML: Insert 10 sample rows (containing duplicates and non-duplicates)
-- ==========================================
INSERT INTO loan_snapshots (loan_account_number, snapshot_timestamp, loan_amount, status) VALUES
-- Loan 'LN-001': Two records on Aug 1, 2026 (Duplicate scenario)
('LN-001', '2026-08-01 08:00:00', 50000.00, 'ACTIVE'),
('LN-001', '2026-08-01 16:30:00', 50000.00, 'ACTIVE'), -- Most recent for Aug 1

-- Loan 'LN-001': Single record on Aug 2, 2026 (Non-duplicate)
('LN-001', '2026-08-02 09:15:00', 49500.00, 'ACTIVE'),

-- Loan 'LN-002': Three records on Aug 1, 2026 (Multiple duplicates)
('LN-002', '2026-08-01 09:00:00', 120000.00, 'PENDING'),
('LN-002', '2026-08-01 12:00:00', 120000.00, 'PENDING'),
('LN-002', '2026-08-01 18:00:00', 120000.00, 'ACTIVE'), -- Most recent for Aug 1

-- Loan 'LN-002': Two records on Aug 2, 2026 (Duplicate scenario)
('LN-002', '2026-08-02 10:30:00', 120000.00, 'ACTIVE'),
('LN-002', '2026-08-02 14:00:00', 120000.00, 'ACTIVE'), -- Most recent for Aug 2

-- Loan 'LN-003': Single records across different days (Non-duplicates)
('LN-003', '2026-08-01 11:00:00', 75000.00, 'ACTIVE'),
('LN-003', '2026-08-02 11:00:00', 75000.00, 'ACTIVE');

INSERT INTO loan_snapshots (loan_account_number, snapshot_timestamp, loan_amount, status) VALUES
-- Loan 1001: Non-duplicate on Aug 1, 2 duplicates on Aug 2
('LN-1001', '2026-08-01 10:00:00', 50000.00, 'ACTIVE'),
('LN-1001', '2026-08-02 09:00:00', 50000.00, 'ACTIVE'),
('LN-1001', '2026-08-02 14:30:00', 50000.00, 'ACTIVE'), -- Duplicate on same day

-- Loan 1002: 3 duplicates on Aug 1, Non-duplicate on Aug 3
('LN-1002', '2026-08-01 08:00:00', 120000.00, 'PENDING'),
('LN-1002', '2026-08-01 11:00:00', 120000.00, 'PENDING'),
('LN-1002', '2026-08-01 16:00:00', 120000.00, 'PENDING'), -- Duplicate
('LN-1002', '2026-08-03 10:00:00', 120000.00, 'ACTIVE'),

-- Loan 1003: Non-duplicates across multiple days
('LN-1003', '2026-08-01 09:30:00', 75000.00, 'ACTIVE'),
('LN-1003', '2026-08-02 09:30:00', 75000.00, 'ACTIVE'),
('LN-1003', '2026-08-03 09:30:00', 75000.00, 'ACTIVE'),

-- Loan 1004: 2 duplicates on Aug 2, 2 duplicates on Aug 4
('LN-1004', '2026-08-02 10:15:00', 30000.00, 'CLOSED'),
('LN-1004', '2026-08-02 15:45:00', 30000.00, 'CLOSED'), -- Duplicate
('LN-1004', '2026-08-04 11:00:00', 30000.00, 'ACTIVE'),
('LN-1004', '2026-08-04 16:30:00', 30000.00, 'ACTIVE'), -- Duplicate

-- Loan 1005: Single entry on Aug 5
('LN-1005', '2026-08-05 12:00:00', 95000.00, 'ACTIVE'),

-- Loan 1006: 3 duplicates on Aug 6
('LN-1006', '2026-08-06 08:30:00', 45000.00, 'PENDING'),
('LN-1006', '2026-08-06 12:00:00', 45000.00, 'PENDING'),
('LN-1006', '2026-08-06 17:15:00', 45000.00, 'PENDING'), -- Duplicate

-- Loan 1007: Non-duplicates
('LN-1007', '2026-08-01 14:00:00', 60000.00, 'ACTIVE'),
('LN-1007', '2026-08-02 14:00:00', 60000.00, 'ACTIVE'),

-- Loan 1008: 2 duplicates on Aug 7
('LN-1008', '2026-08-07 09:00:00', 110000.00, 'ACTIVE'),
('LN-1008', '2026-08-07 15:00:00', 110000.00, 'ACTIVE'), -- Duplicate

-- Loan 1009: Non-duplicate on Aug 7
('LN-1009', '2026-08-07 10:30:00', 85000.00, 'ACTIVE'),

-- Loan 1010: 2 duplicates on Aug 8
('LN-1010', '2026-08-08 08:00:00', 25000.00, 'PENDING'),
('LN-1010', '2026-08-08 12:30:00', 25000.00, 'PENDING'), -- Duplicate

-- Extra filler rows to reach exactly 30 total sample rows
('LN-1001', '2026-08-03 10:00:00', 50000.00, 'ACTIVE'),
('LN-1002', '2026-08-04 10:00:00', 120000.00, 'ACTIVE'),
('LN-1003', '2026-08-04 09:30:00', 75000.00, 'ACTIVE'),
('LN-1005', '2026-08-06 12:00:00', 95000.00, 'ACTIVE');


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


-- ==========================================
-- 3. SQL QUERY: Select the most recent record per loan per day
-- ==========================================
   
   WITH RankedLoans AS (
    SELECT 
        loan_account_number,
        snapshot_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY loan_account_number, CAST(snapshot_timestamp AS DATE) 
            ORDER BY snapshot_timestamp DESC
        ) as rn
    FROM loan_snapshots
)
SELECT 
    loan_account_number,
    snapshot_timestamp,
    'Yes' AS is_duplicate
FROM RankedLoans
WHERE rn > 1
ORDER BY loan_account_number, snapshot_timestamp;


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

158 )Find users session durations

 158 )Find users session durations 

-- ==========================================

use temp_db;

 use temp_db;

 -- ==========================================

 

 -- 1. Create Table

CREATE TABLE user_login (

    session_id INT AUTO_INCREMENT PRIMARY KEY,

    userid VARCHAR(50) NOT NULL,

    logintime DATETIME NOT NULL,

    logout_time DATETIME NOT NULL

);

-- ==========================================

-- 2. Insert Data

INSERT INTO user_login (userid, logintime, logout_time) VALUES

('user_001', '2026-08-10 08:30:00', '2026-08-10 09:15:00'),

('user_001', '2026-08-12 10:00:00', '2026-08-12 11:30:00'),

('user_002', '2026-08-11 09:00:00', '2026-08-11 09:45:00'),

('user_002', '2026-08-13 13:00:00', '2026-08-13 14:30:00'),

('user_003', '2026-08-09 14:10:00', '2026-08-09 15:00:00'),

('user_003', '2026-08-14 11:15:00', '2026-08-14 12:00:00'),

('user_001', '2026-08-14 16:20:00', '2026-08-14 17:50:00'),

('user_004', '2026-08-15 08:00:00', '2026-08-15 09:30:00'),

('user_004', '2026-08-15 10:00:00', '2026-08-15 10:45:00'),

('user_002', '2026-08-15 11:00:00', '2026-08-15 12:15:00');

--- ==========================================

- 3. Run Query to Find Average Session Durations in the Last 7 Days

-- ==========================================

 SELECT 

    userid,

    SEC_TO_TIME(

                AVG(    

                    TIME_TO_SEC(

                            TIMEDIFF(

                                    logout_time, logintime)))) AS avg_session_duration,

    AVG(TIMESTAMPDIFF(MINUTE, logintime, logout_time)) AS avg_duration_in_minutes

FROM user_login

WHERE logintime >= NOW() - INTERVAL 7 DAY

GROUP BY userid;

-- ==========================================

# userid, avg_session_duration, avg_duration_in_minutes

'user_001', '01:15:00.0000', '75.0000'

'user_002', '01:10:00.0000', '70.0000'

'user_003', '00:47:30.0000', '47.5000'

'user_004', '01:07:30.0000', '67.5000'

157 ) Snowflake Administrator Interview Questions & Answers

 

156 ) Why do we need Data warehouse

Why do we need Data warehouse, when OLTP application data already there ? 

-----------------------------------------------

 What is Datawarehouse

A data warehouse is a centralized system

that stores structured data from various sources

for reporting and analysis

Data warehouses can:

Consolidate data: Store data from multiple sources into a single source of truth

Store historical data: Store and analyze long-term historical data

Analyze data about a particular subject or functional area:

----------------------------------

Data warehouses are important for a number of reasons, including:

• Centralizing data

  Data warehouses consolidate large amounts of data from multiple sources into a single, central database. This makes it easier to analyze the data and gain valuable insights. 
• Improving decision-making

  Data warehouses help organizations make faster, more informed decisions by providing easy access to high-quality data. 
• Maintaining historical data

  Data warehouses can store months or years of information, which can be useful for trend analysis and forecasting. 
• Securing data

  Data warehouses store data in a single location, which makes it easier to control access and keep data secure. 
• Handling big data

  Data warehouses can help solve big data challenges by making large amounts of information more usable. 
• Optimizing for read access

  Data warehouses are usually optimized for read access, which can result in faster report generation. 

Data warehouses are a critical component of business intelligence systems and data pipelines

155 ) SQL Query - best practices

 SQL Query  best practices

===========================================================

 Key points to remember:

• Select only necessary columns:

  Avoid using SELECT * and explicitly list the columns you need to retrieve only the relevant data. 
• Filter early:

  Apply WHERE clauses as early as possible in the query to reduce the amount of data processed. 
• Use indexes wisely:

  Create indexes on frequently used columns in WHERE clauses to speed up lookups, but be cautious of over-indexing which can slow down writes. 
• Optimize joins:

  Choose the appropriate JOIN type (INNER JOIN, LEFT JOIN, etc.) based on your data relationships and avoid unnecessary joins. 
• Minimize subqueries:

  Try to rewrite queries to avoid nested subqueries where possible as they can be inefficient. 
• Consider data types:

  Use the most appropriate data type for each column to optimize storage and comparison operations. 
• Analyze execution plans:

  Regularly review the query execution plan to identify potential bottlenecks and optimize accordingly. 
• Use stored procedures:

  For complex logic or frequently used queries, consider using stored procedures to improve performance and maintainability. 

Other important practices:

• Partitioning and sharding:

  For very large datasets, consider partitioning tables by date or other relevant criteria to improve query performance on specific subsets. 
• Avoid unnecessary calculations:

  Perform calculations only when needed and avoid redundant computations within the query. 
• Use UNION ALL instead of UNION:

  When combining results from multiple queries, use UNION ALL if you don't need to remove duplicates. 
• Monitor query performance:

  Implement monitoring tools to track query execution times and identify potential performance issues. 
• Optimize for your database system:

  Understand the specific optimization features and best practices available for your database platform. 
•