What is CTE in SQL?

A Common Table Expression (CTE) is a temporary, named result set that you define within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Think of it as a "temporary view" that exists only for the duration of one query.

They are primarily used to simplify complex joins and subqueries, making your SQL code more readable and maintainable.

Key characteristics of CTEs: 

• Temporary: CTEs exist only for the duration of the query in which they are defined. They are not stored as objects in the database. 
• Named: They are given a name using the WITH clause, allowing for clear identification and referencing within the main query or subsequent CTEs. 
• Reusable: A CTE can be referenced multiple times within the same query, avoiding repetitive code and improving maintainability. 
• Recursive capabilities: CTEs can be self-referential, enabling the processing of hierarchical data structures (e.g., organizational charts and bills of materials).

When to use a CTE?

Use CTE when:

• SQL Query becomes long and complicated.
• You need to reuse a subquery multiple times.
• You want to perform recursive operations.
• You want to break a complex query into steps.
• You want to reference the result set multiple times.

Let's understand how to write CTE with a few examples.

Example 1: Simple CTE to Filter Employees.

This SQL CTE will filter the list of employees getting high salaries.

WITH HighSalaryEmployees AS (
    SELECT EmployeeId, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighSalaryEmployees;

This logic is cleaner and reusable.

Example 2: CTE used multiple times.

In this example, we are creating a CTE to fetch sales records of each employee and then filtering them out based on conditions.

WITH SalesCTE AS (
    SELECT EmployeeId, TotalSales
    FROM Sales
)
SELECT * FROM SalesCTE WHERE TotalSales > 100000;

-- reuse same CTE again
SELECT EmployeeId, TotalSales * 0.10 AS Bonus
FROM SalesCTE;

Here, CTE is used in two different select queries, which shows that it is reusable code and can use multiple times.

Example 3: Recursive CTE.

This SQL uses a recursive CTE to build an employee hierarchy (CEO → Managers → Employees) and shows each employee with their hierarchy level.

WITH EmpHierarchy AS (
    -- Anchor Query (Top-Level)
    SELECT EmployeeId, Name, ManagerId, 0 AS Level
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive Query
    SELECT e.EmployeeId, e.Name, e.ManagerId, h.Level + 1
    FROM Employees e
    INNER JOIN EmpHierarchy h
        ON e.ManagerId = h.EmployeeId
)
SELECT * FROM EmpHierarchy;

After executing the above query, your result set will look like this:

In one of my interviews, the interviewer asked me how we can use CTE to perform INSERT, UPDATE, or DELETE queries. At that time, I was not sure whether we could perform these operations inside CTE or not. So the correct answer is no, you cannot directly insert these queries inside CTE, but you can use CTE while performing these operations.

Note: CTE must contain only a SELECT.

Let's check a few examples:

Example 4: INSERT Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
INSERT INTO BonusTable(EmployeeId, BonusAmount)
SELECT EmployeeId, Salary * 0.1
FROM EmpCTE;

Example 5: UPDATE Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId, Salary
    FROM Employees
    WHERE Salary < 30000
)
UPDATE EmpCTE
SET Salary = Salary + 2000;

Example 6: DELETE Query Using CTE.

WITH EmpCTE AS (
    SELECT EmployeeId
    FROM Employees
    WHERE Salary < 10000
)
DELETE FROM EmpCTE;

Why can a CTE not contain INSERT, UPDATE, or DELETE?

A CTE (Common Table Expression) is not a table and is not stored anywhere.

It is only a temporary result set that exists only for the duration of the next SELECT, INSERT, UPDATE, or DELETE statement.

Because of this:

❌ CTE can only generate data using a SELECT.

❌ It cannot perform data-modifying operations inside itself.

Logical Answer: A CTE is like a named subquery.

Subqueries cannot contain:

• INSERT
• UPDATE
• DELETE

They only return a dataset.

Since CTE is just a subquery with a name, it also cannot perform DML inside the definition.

Observer Design Pattern in C#.

In modern software applications, it’s common to have multiple components that need to react whenever something changes, like a weather display updating when the temperature changes, or different modules getting notified when an order is placed. Managing these updates manually quickly becomes messy and tightly coupled. This is where the Observer Design Pattern shines. It provides a clean, event-driven way for one object to notify many others automatically, making applications flexible, maintainable, and scalable.

What is the Observer Design Pattern?

The Observer Design Pattern is a behavioral design pattern used when you need one object (the Subject) to automatically notify other objects (Observers) of changes to its state.

This creates a one-to-many relationship between objects, where the Subject keeps a list of its Observers and notifies them whenever something changes.

Simple Words: Observer Pattern allows an object to publish events and multiple subscribers (observers) to react automatically.

When Do We Need the Observer Design Pattern?

Without the Observer Pattern:

• You would manually call update functions for all dependent objects.
• Code becomes tightly coupled.
• Adding or removing listeners requires modifying the Subject class.
• Any change breaks the Open/Closed Principle.

With Observer Pattern:

• The subject doesn’t need to know who is observing.
• Observers subscribe/unsubscribe on their own.
• Adding new observers requires zero changes in the Subject.
• It's event-driven and loosely coupled.

Real-Life Analogy: Think of it like a YouTube channel is a subject, and Subscribers are Observers. Whenever a channel uploads a new video, the YouTube channel notifies all your subscribers automatically. The channel doesn’t care how many people subscribed or who they are. Subscribers get updates based on their interests. 

This is exactly how the Observer Pattern works.

Observer Design Pattern Example.

Here is a C# example demonstrating the Observer pattern:

namespace PracticeCode.DesignPattern
{
    //Observer Interface
    public interface IObserver
    {
        void Update(float temperature);
    }
    //Subject Interface
    public interface ISubject
    {
        void RegisterObserver(IObserver observer);
        void RemoveObserver(IObserver observer);
        void NotifyObserver();
    }

    //Concrete Subject – Weather Station
    public class WeatherStation : ISubject
    {
        private List<IObserver> observers = new();
        private float temperature;

        public void RegisterObserver(IObserver observer)
        {
            observers.Add(observer);
        }
        public void RemoveObserver(IObserver observer)
        {
            observers.Remove(observer);
        }
        public void NotifyObserver()
        {
            foreach(var observer in observers)
            {
                observer.Update(temperature);
            }
        }

        //When temp change notify everyone
        public void SetTemperature(float newTemp)
        {
            Console.WriteLine($"\nWeatherStation: New Temperature = {newTemp}°C");
            temperature = newTemp;
            NotifyObserver();
        }
    }
    //Concrete Observers – Displays
    public class DigitalDisplay : IObserver
    {
        public void Update(float temperature)
        {
            Console.WriteLine($"Digital Display -> Updated Temperature: {temperature}°C");
        }
    }
    public class MobileDisplay : IObserver
    {
        public void Update(float temperature)
        {
            Console.WriteLine($"Mobile Display -> Updated Temperature: {temperature}");
        }
    }
}

Client Code in Program.cs, where the new observer is getting registered and getting notified whenever there is an update in temperature.

//Observers Subscribe
station.RegisterObserver(digital);
station.RegisterObserver(mobile);

//Observers Notify
station.SetTemperature(29.4f);
station.SetTemperature(30.2f);

//Observer Removed
station.RemoveObserver(digital);

//Observer Notify
station.SetTemperature(32.0f);

Output:

WeatherStation: New Temperature = 28.5°C
Digital Display → Updated Temperature: 28.5°C
Mobile App → Temperature Alert: 28.5°C

WeatherStation: New Temperature = 30.2°C
Digital Display → Updated Temperature: 30.2°C
Mobile App → Temperature Alert: 30.2°C

WeatherStation: New Temperature = 31.7°C
Digital Display → Updated Temperature: 31.7°C

This is one small example of how an Observer Design Pattern helps you write better and maintainable code, and in which all real-life conditions in which you can use this pattern.

These are some key points that you can keep in mind while writing an Observer Design Pattern Code:

• Type: Behavioral
• Purpose: Notify multiple objects automatically when one object changes.
• Relationship: One-to-many
• Helps With: Loose coupling, event-driven architecture
• Key Methods: Register, Remove, Notify
• Real Use Cases: Events, UI updates, stock market tickers, notifications

In Short, the Observer Pattern lets you build a system where one object publishes updates and many other objects automatically react to those updates.

Factory Method Pattern in C#.

The Factory Design Pattern in C# is a creational design pattern that provides an interface for creating objects in a superclass while allowing subclasses to alter the types of objects that are created. This pattern encapsulates object creation logic, decoupling it from the client code that uses the objects.

It is beneficial for situations requiring flexibility, such as adding new product types without modifying existing client code.

When to use the Factory pattern?

• Uncertainty about object type: When a class cannot predict the type of objects it needs to create, and this decision is better made at runtime.
• Subclasses decide instantiation: When you want to delegate the responsibility of creating objects to subclasses, allowing them to alter the type of objects created.
• Encapsulating creation logic: When the process of creating an object is complex, repetitive, or has many dependencies, the factory pattern can encapsulate this logic, making the code cleaner.
• Extending with new products: When you want to easily add new product types in the future. Instead of modifying the client code, you can simply create a new subclass and implement its creation logic within the factory.
• Decoupling client from concrete classes: To decouple the client code from the concrete classes it instantiates. The client only interacts with the factory and a common interface, not the specific implementations.

Let's understand a real-life problem and how we can fix that using the Factory Method.

Bad Example: Notification System.

Your application is using a Notification System to send notifications to users. Initially, you were sending only an Email Notification, so it was simple and easy. Later, you added SMS and a Push notification system. You have written if-else conditions to create different kinds of objects based on user requirements.

Example Code:

if (notification == "Email") new EmailNotification();
else if (notification == "SMS") new SMSNotification();
else if (notification == "PUSH") new PUSHNotification();

This is a bad approach to writing code because adding a new payment method means changing existing code, → violates the Open/Closed Principle. This code is also difficult to test and tightly coupled.

You might feel like you are writing too much code for a simple task, but in real-world, large and complex projects, the Factory Method Pattern helps you maintain, extend, and scale your application without modifying existing code.

Good Example: Notification System Using Factory Method.

fff

Step 1: Notification Interface/Abstract Class: Defines the common interface or abstract class for the objects that the factory will create. This ensures that all concrete products share a common contract.

public interface INotification
{
    void Send(string to, string message);
}

Step 2: Concrete Product Classes: Implement the INotification interface or inherit from the abstract product class, providing specific implementations.

public class EmailNotification : INotification
{
    public void Send(string to, string message)
    {
        Console.WriteLine($"Sending Email to {to} : {message}");
    }
}
public class SMSNotification : INotification
{
    public void Send(string to, string message)
    {
        Console.WriteLine($"Sending SMS to {to} : {message}");
    }
}
public class PUSHNotification : INotification
{
    public void Send(string to, string message)
    {
        Console.WriteLine($"Sending PUSH to {to} : {message}");
    }
}

Step 3: Creator/Factory Class (Abstract or Concrete): Declares the factory method, which returns an object of the INotification type. This method can be abstract, forcing subclasses to implement it, or it can provide a default implementation.

public abstract class NotificationFactory
{
    public abstract INotification CreateNotification();
}

Step 4: Concrete Creator/Factory Classes: Override the factory method to return instances of specific concrete product classes.

public class EmailFactory : NotificationFactory
{
    public override INotification CreateNotification()
    {
        return new EmailNotification();
    }
}
public class SMSFactory : NotificationFactory
{
    public override INotification CreateNotification()
    {
        return new SMSNotification();
    }
}
public class PUSHFactory : NotificationFactory
{
    public override INotification CreateNotification()
    {
        return new PUSHNotification();
    }
}

Client using the Factory:

NotificationFactory factory;
factory = new EmailFactory();
var email = factory.CreateNotification();
email.Send("user@example.com", "Welcome Email!");

I hope you now understand how and when to use the Factory Method Design Pattern.

The  Factory Method Pattern lets subclasses decide which object to create, helping you remove direct new calls, simplify object creation, and extend your application without modifying existing code.

Singleton Design Pattern n C#.

The Singleton Design Pattern is one of the simplest yet most important creational design patterns in software engineering. It ensures that only one instance of a class exists in memory throughout the application's lifetime and provides a global point of access to that instance.

What is the Singleton Design Pattern?

A Singleton class allows only one object of itself to be created and shared across the entire application. This pattern is extremely useful when you need to:

• Manage shared resources (like logging, configuration, cache, or thread pool).
• Avoid duplicate object creation for performance reasons.
• Maintain a single source of truth.

Think of a printer spooler or Windows Task Manager. You can open it multiple times, but under the hood, only one instance manages all print jobs or processes. That’s a Singleton in action, one object serving the whole system.

Does who don't know: The printer spooler service is a Windows service that manages and queues print jobs for your printer.

Key Characteristics 

• Only one instance of the class is created. 
• Global access to that instance through a public static property or method. 
• Thread-safety is ensured in multi-threaded environments. 
• Lazy initialization means the object is created only when needed.

How To Implement the Singleton Design Pattern in C#?

There are multiple ways to implement the Singleton design pattern in C#, and here we are going to discuss all, and at the end will decide which one is best and easiest to implement.

Approach 1: Non-Thread Safe Implementation.

This simple Singleton creates only one instance of the Singleton class. It uses a private static field _instance to store that single object. The constructor is private, so no one can create the object using new. The GetInstance() method checks if it _instance is null — if yes, creates the object; if not, returns the existing one.

Example Code:

public class Singleton
{
    private static Singleton? _instance;

    private Singleton() { }

    public static Singleton GetInstance()
    {
        if (_instance == null)
            _instance = new Singleton();
        return _instance;
    }

    public void Message(string message)
    {
        Console.WriteLine($"{DateTime.Now}: {message}");
    }
}

Using a Singleton Class in a Program.cs

//Singleton Design
var instance1 = Singleton.GetInstance();
var instance2 = Singleton.GetInstance();

if(instance1 == instance2) Console.WriteLine("True- Both refer to same instance.");

instance1.Message("Log from Instance 1");
instance2.Message("Log from Instance 2");

Output:

True- Both refer to same instance.
28-11-2025 16:39:40: Log from Instance 1
28-11-2025 16:39:40: Log from Instance 2

This version isn’t thread-safe. If two threads call GetInstance() Simultaneously, two objects could be created. Let's understand another approach to make our code thread-safe safe so only one instance is possible because that is our goal in a singleton.

Approach 2: Thread-Safe Singleton.

A Thread-Safe Singleton ensures that only one instance of a class is created, even when multiple threads try to create it at the same time. It prevents race conditions and guarantees that all threads receive the same shared instance safely.

Example Code:

public class Singleton
{
    private static Singleton? _instance;
    private static readonly object _lock = new object();

    //private constructor to prevents extension instantiation
    private Singleton()
    {
        Console.WriteLine("Instance Created.");
    }

    public static Singleton GetInstance()
    {
        //1st check without lock
        if(_instance == null)
        {
            lock (_lock) //only one thread can enter at a time
            {
                //2nd check with lock
                if(_instance == null)
                {
                    _instance = new Singleton();
                }
            }
        }
        return _instance;
    }
}

When using a non-thread-safe Singleton, two threads can reach the line if(_instance == null) at the same time. Both threads see that the instance is null and both try to create the object. In double-checked locking, Thread A enters the lock first and creates the Singleton instance, while Thread B waits outside the lock.

Once Thread A finishes creating the instance and leaves the lock, Thread B enters the lock. If we didn’t check _instance again inside the lock, Thread B would also create a new instance, overwriting the first one.

Multiple Threads Calling It:

Thread t1 = new Thread(() =>
{
    var s1 = Singleton.GetInstance();
});

Thread t2 = new Thread(() =>
{
    var s2 = Singleton.GetInstance();
});

t1.Start();
t2.Start();

t1.Join();
t2.Join();

Output:

Instance Created.

Even with 2 (or 100) threads, the constructor runs only once.

The above approach solves my multiple-thread problem, but it is a lot of code to write, and handling threads manually is not an easy job. So, .NET provides a better solution to implement Singleton more easily and effectively. Let's discuss that now.

Approach 3: Singleton Using .NET Lazy<T>

Lazy<T> is a special .NET class that automatically handles lazy initialization + thread safety for you. It ensures the Singleton object is created only when accessed, and only once, even if multiple threads call it at the same time.

Does Who Don't Know: Lazy initialization is a technique used to delay the creation of an object until it is actually needed. This can improve performance and reduce memory usage, especially for objects that are expensive to create or may not be used at all during the program's execution.

Example Code:

public sealed class Singleton
{
    // Lazy ensures thread-safe, lazy initialization
    private static readonly Lazy<Singleton> _instance =
        new Lazy<Singleton>(() => new Singleton());

    // Private constructor prevents external creation
    private Singleton()
    {
        Console.WriteLine("Singleton Instance Created");
    }

    // Public accessor to get the single instance
    public static Singleton Instance => _instance.Value;

    public void ShowMessage(string msg)
    {
        Console.WriteLine("Message: " + msg);
    }
}

Calling inside Program.cs

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("Accessing Singleton First Time:");
        Singleton s1 = Singleton.Instance;
        s1.ShowMessage("Hello from First Instance");

        Console.WriteLine("\nAccessing Singleton Second Time:");
        Singleton s2 = Singleton.Instance;
        s2.ShowMessage("Hello from Second Instance");

        Console.WriteLine($"\nAre both instances same?  { (s1 == s2) }");
    }
}

Output:

Accessing Singleton First Time:
Singleton Instance Created
Message: Hello from First Instance

Accessing Singleton Second Time:
Message: Hello from Second Instance

Are both instances same?  True

This approach is fully thread-safe with cleaner code and no manual lock required.

When to Use Singleton Pattern

Use Singleton when:

• You need exactly one instance for coordination (e.g., logging service, configuration manager).
• Creating multiple instances would cause conflicts or inconsistency.
• You need shared state or cache across different parts of the app.

When Not to Use Singleton

Avoid Singleton when:

• Your class maintains mutable state - it can cause unexpected side effects.
• It leads to tight coupling - other classes depend directly on the singleton.
• It complicates unit testing (since global state can persist across tests).

The Singleton Design Pattern is powerful and useful but like all global patterns, it must be used wisely. Overusing it can lead to tight coupling and hidden dependencies. However, when applied correctly for shared resources or configuration objects it provides an elegant, thread-safe way to ensure a single source of truth across your application.

Google Play Store Rolls Out AI-Generated Summaries to Simplify App Review Skimming.

The Google Play Store is rolling out a new feature powered by generative AI, designed to save users significant time when researching apps. The platform has begun displaying AI-generated summaries of aggregated user reviews, providing a quick snapshot of public opinion.

This functionality has been in development for some time, with initial signs of its arrival noted over a year ago. The feature is now starting to appear for a broader audience, indicating a general rollout phase.

Cutting Through the Noise of Thousands of Reviews.

The primary role of this new AI tool is to process the large volume of user feedback often associated with popular apps. By absorbing this extensive body of text, the AI can then serve up the information in a concise, easily digestible format.

When available, users will find these summaries under a bolded "Users are saying" heading within the Ratings and reviews section of an app's listing. The summary typically takes the form of a single, informative paragraph.

This paragraph aims to highlight the most common feedback points. Crucially, it attempts to give a balanced view by consolidating both the positive aspects and the prevailing negative points mentioned across the collective user reviews.

Enhancing User Experience and Feature Discovery.

Beyond the paragraph summary, the feature is further enhanced by interactive chips displayed below the text. These chips allow users to "drill down" into the reviews.

Tapping on a chip will filter the results to only show reviews that specifically mention that particular aspect of the app. This makes it easier for potential users to quickly judge features like performance, interface, or specific functionality.

This move by Google follows a trend in the tech industry to use AI for better content aggregation. Amazon has utilized AI-generated product review summaries for some time, and Apple rolled out a similar review summarization feature on the iOS App Store in April.

The new feature is being observed on various devices, though it is not yet universally available across all accounts. This gradual rollout is typical for major Play Store updates.

Google Photos Rolls Out 'Trendy' Video Templates for Easy, Automated Video Creation.

Google Photos is set to drastically simplify the video creation process for its users. The company is introducing a suite of new "trendy" video editing templates, aimed at making it easier than ever to turn a collection of personal memories into a polished, share-worthy video clip.

This significant update is an extension of the existing 'Highlight video' function already found within the application's 'Create' tab. It represents a clear effort by Google to democratize the video editing experience for its massive user base.

What the New Templates Offer.

These new templates are essentially designed as pre-packaged video structures. They allow users to simply slot their photos and video clips into a pre-set, professional-looking visual narrative.

Google has positioned this new tool as a way to offer "easy and automated" video creation for everyone. This removes the barrier of manually aligning clips and music, which can often be time-consuming for casual users.

A key technical aspect of the new feature is the automatic synchronization capabilities. Each template comes pre-loaded with music, ensuring cuts and transitions are precisely beat-matched to the audio rhythm.

Furthermore, the templates include designated placeholders for text overlays. This feature allows users to easily add context, titles, or a complete narrative to their video story.

How to Use the Feature.

The process for using the new templates is straightforward, according to the official support documentation. Users will begin by navigating to the 'Create' tab on their Google Photos mobile app.

From there, they select 'Highlight video' where they can now browse through templates for different themes and narratives. Once a template is selected, users simply tap 'Use template.'

Content selection can be done manually by picking the best photos and videos from the gallery. Alternatively, users can leverage an "assisted content selection" option, which uses AI to select the most relevant media for the chosen template.

A Focus on Share-Worthy Content.

While the official support page details the full step-by-step process, a widespread public rollout of the new templates does not yet appear to be live for all users. The feature is expected to arrive on Android devices first.

This move is a clear step by Google to enhance the media editing capabilities within Photos. It makes high-quality video creation accessible, enabling casual users to produce "polished, share-worthy" content with minimal effort.