Data-modeling-made-easy

Saturday, August 8, 2026

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 ) query to find out all sessions ,durations of a user in a portal

use temp_db;

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

-- 1. DDL: Create the user_sessions table

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

CREATE TABLE user_sessions (

session_id INT PRIMARY KEY,

user_id INT NOT NULL,

session_timestamp TIMESTAMP NOT NULL,

session_duration INT NOT NULL -- duration in minutes

);

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

-- 2. DML: Insert 10 sample rows

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

INSERT INTO user_sessions (session_id, user_id, session_timestamp, session_duration) VALUES

(1, 101, '2026-08-01 09:30:00', 30),

(2, 101, '2026-08-01 14:15:00', 45), -- User 101 has two sessions on Aug 1

(3, 101, '2026-08-02 10:00:00', 60),

(4, 102, '2026-08-02 11:30:00', 20),

(5, 101, '2026-08-03 16:45:00', 15),

(6, 103, '2026-08-04 08:00:00', 90),

(7, 102, '2026-08-05 13:20:00', 40),

(8, 101, '2026-08-06 19:10:00', 50),

(9, 103, '2026-08-07 21:00:00', 30),

(10, 101, '2026-08-08 08:30:00', 25);

INSERT INTO user_sessions (session_id, user_id, session_timestamp, session_duration) VALUES

-- User 101 sessions on Aug 1 and Aug 2

(1, 101, '2026-08-01 08:15:00', 25),

(2, 101, '2026-08-01 11:30:00', 40),

(3, 101, '2026-08-01 14:00:00', 15),

(4, 101, '2026-08-01 17:45:00', 60),

(5, 101, '2026-08-01 21:10:00', 30),

(6, 101, '2026-08-02 09:00:00', 45),

(7, 101, '2026-08-02 12:20:00', 20),

(8, 101, '2026-08-02 15:10:00', 35),

(9, 101, '2026-08-02 18:30:00', 50),

(10, 101, '2026-08-02 22:00:00', 10),

-- User 102 sessions on Aug 1 and Aug 2

(11, 102, '2026-08-01 07:30:00', 15),

(12, 102, '2026-08-01 10:00:00', 30),

(13, 102, '2026-08-01 13:15:00', 45),

(14, 102, '2026-08-01 16:40:00', 25),

(15, 102, '2026-08-01 19:20:00', 60),

(16, 102, '2026-08-02 08:45:00', 20),

(17, 102, '2026-08-02 11:10:00', 40),

(18, 102, '2026-08-02 14:30:00', 35),

(19, 102, '2026-08-02 17:00:00', 15),

(20, 102, '2026-08-02 20:15:00', 50);

-- truncate user_sessions;

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

-- 3. SELECT STATEMENT: Rolling 7-day sum

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

WITH daily_user_activity AS (

SELECT

user_id,

CAST(session_timestamp AS DATE) AS session_date,

COUNT(*) AS total_sessions,

SUM(session_duration) AS total_duration

FROM

user_sessions

GROUP BY

user_id,

CAST(session_timestamp AS DATE)

)

SELECT

user_id,

session_date,

total_sessions,

total_duration

FROM

daily_user_activity

ORDER BY

user_id,

session_date;

Thursday, August 6, 2026

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 ?

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

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 optimization best practices

SQL Query optimization 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.

154 ) SQL Concept questions

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

sql : union union all difference

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

The Short Answer: SQL UNION vs. UNION ALL. The key difference is that UNION removes duplicate records, whereas UNION ALL includes all duplicates.

This distinction not only changes the number of rows in the query result, but it also impacts performance

so always use UNION ALL instead of UNION to speed up the execution time

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

DIFFERENCE between joins and union

joins combine data side-by-side, whereas unions combine data one on top of the other

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

VARCHAR AND NVARCHAR difference

In database contexts, VARCHAR and NVARCHAR are data types used to store variable-length text strings.

The key difference lies in their encoding:

VARCHAR uses single-byte character sets (like ASCII),

while NVARCHAR

uses Unicode, allowing for a wider range of characters including those from multiple languages. NVARCHAR typically requires more storage space and has a shorter maximum character length compared to VARCHAR

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

153 ) List of Chellenges of Data modeling ?

Problems / Chellenges of Data modeling ?

Data modeling can face a number of challenges, including:

Data quality: Data may be missing, incorrect, or inconsistent, and it can be difficult to maintain data quality over time.
Data security: The many interconnected data sources make it vulnerable to attacks from hackers.
Integrating diverse data sources: Data from different sources may be in different structures, schemas, and formats. It's important to make sure the data is cleaned and transformed correctly before loading it into a hub.
Scalability: Big data can be enormous, and the system may run too slowly or be unable to handle heavy pressure. Cloud computing can help with this challenge.
Choosing the right data model: It can be challenging to choose the right data model.
Balancing normalization and denormalization: It can be challenging to balance normalization and denormalization.
Handling data changes and evolution: It can be challenging to handle data changes and evolution.
Communicating and collaborating with stakeholders: It can be challenging to communicate and collaborate with stakeholder

PAGES

Saturday, August 8, 2026

Thursday, August 6, 2026