WITH RankedSalaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rnk = 2;

Output: The second highest salary value.

#84. Rank employees by salary within each department.
A: Use RANK() or DENSE_RANK() with PARTITION BY department_id.

SELECT
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

Output: Employees ranked by salary within their department.

#85. What is the difference between RANK() and DENSE_RANK()?
A:
• RANK(): Assigns a rank to each row within its partition. If there are ties, it assigns the same rank and skips the next rank(s). (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns a rank to each row within its partition. If there are ties, it assigns the same rank but does not skip the next rank(s). (e.g., 1, 2, 2, 3).

#86. Get the previous order_date for each customer's orders.
A: Use the LAG() window function.

SELECT
    customer_id,
    order_id,
    order_date,
    LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date
FROM orders;

Output: Each order, with the date of the customer's preceding order.

#87. What is the NTILE(n) window function?
A: NTILE(n) divides the rows in a partition into n groups, and assigns a number from 1 to n to each group. It attempts to make the groups as equal in size as possible.

-- Divide employees into 4 salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

Output: Employees assigned to one of four salary quartiles.

#88. What is the ROW_NUMBER() window function?
A: ROW_NUMBER() assigns a unique, sequential integer to each row within its partition, starting from 1. It does not skip numbers in case of ties.

SELECT
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;

Output: Employees with a unique row number within their department, based on salary.

#89. What is an INDEX in SQL? Why is it important?
A: An index is a special lookup table that the database search engine can use to speed up data retrieval. It works like an index in a book, allowing the database to quickly find data without scanning the entire table.
Importance:
• Significantly improves the performance of SELECT queries, especially on large tables.
• Speeds up WHERE clauses, ORDER BY, GROUP BY, and JOIN operations.
• However, it can slow down INSERT, UPDATE, and DELETE operations as the index also needs to be updated.

#90. What is a VIEW in SQL? When would you use it?
A: A VIEW is a virtual table based on the result-set of a SQL query. It does not store data itself but instead retrieves it from the underlying tables whenever the view is queried.
Use Cases:
• Security: Restrict access to specific rows or columns.
• Simplicity: Simplify complex queries (e.g., joins, aggregations) into a single logical table.
• Consistency: Present a consistent view of data even if underlying table structures change.
---
#91. What is the COALESCE() function?
A: The COALESCE() function returns the first non-NULL expression in a list of arguments. It's useful for providing default values when a column might be NULL.

SELECT name, COALESCE(commission, 0) AS actual_commission
FROM employees;

Output: Employee names, with 0 instead of NULL if commission is missing.

#92. What is UNION vs UNION ALL?
A:
• UNION: Combines the result sets of two or more SELECT statements and removes duplicate rows. It sorts the combined result.
• UNION ALL: Combines the result sets of two or more SELECT statements and includes all rows, including duplicates. It does not sort the result, making it generally faster.

#93. Find all employee_ids that appear in both employees and contractors tables.
A: Use the INTERSECT operator.

-- Tables: employees (employee_id, name), contractors (contractor_id, name)
SELECT employee_id FROM employees
INTERSECT
SELECT contractor_id FROM contractors;

Output: Employee/contractor IDs that exist in both tables.

#94. Find employee_ids that are in employees but NOT in contractors tables.
A: Use the EXCEPT (or MINUS in Oracle) operator.

SELECT employee_id FROM employees
EXCEPT
SELECT contractor_id FROM contractors;

Output: Employee IDs present in 'employees' but not in 'contractors'.

#95. What is a PRIMARY KEY?
A: A PRIMARY KEY is a column (or set of columns) that uniquely identifies each row in a table.
• It must contain unique values.
• It cannot contain NULL values.
• A table can have only one primary key.

#96. What is a FOREIGN KEY?
A: A FOREIGN KEY is a column (or set of columns) in one table that refers to the PRIMARY KEY in another table. It establishes a link or relationship between two tables, enforcing referential integrity.

#97. How do you detect duplicate records in a table?
A: Use GROUP BY with COUNT() and HAVING.

SELECT col1, col2, COUNT(*)
FROM my_table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Output: Rows (or groups of columns) that appear more than once.

#98. How would you delete duplicate records from a table, keeping one instance?
A: This can be done using a CTE with ROW_NUMBER().

WITH CTE_Duplicates AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY (SELECT NULL)) as rn -- Partition by columns that define a duplicate
    FROM my_table
)
DELETE FROM CTE_Duplicates
WHERE rn > 1;

Output: Duplicate rows are removed, leaving one instance for each set of duplicates.

#99. What is NULL in SQL?
A: NULL represents a missing or unknown value. It is not equivalent to zero, an empty string, or false. Any arithmetic or comparison operation involving NULL generally results in NULL. Special operators like IS NULL and IS NOT NULL are used to check for NULL values.

#100. Write a query to find the gap between order_dates for each customer.
A: Use LAG() to get the previous order_date and then calculate the difference.

SELECT
    customer_id,
    order_id,
    order_date,
    LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order_date,
    order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last_order
FROM orders
ORDER BY customer_id, order_date;

Output: Each order, with the date of the previous order and the number of days between them.

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

❔ Interview question

When would you use the __slots__ attribute in a Python class, and what is its main trade-off?

Answer: The __slots__ attribute is used for memory optimization. By defining it in a class, you prevent the creation of a __dict__ for each instance, instead allocating a fixed amount of space for the specified attributes. This is highly effective when creating a large number of objects. The primary trade-off is that you lose the ability to add new attributes to instances at runtime.

tags: #python #interview

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

🧠 Quiz: What is the most Pythonic way to create a new list containing the squares of numbers from 0 to 4?

A) squares = [x**2 for x in range(5)]
B) squares = list(map(lambda x: x**2, range(5)))
C) squares = []
for x in range(5):
squares.append(x**2)

✅ Correct answer: A

Explanation: List comprehensions are a concise and highly readable way to create lists from other iterables. While the other options work, a list comprehension is generally considered the most "Pythonic" for its clarity and efficiency in this context.

#Python #ProgrammingTips #CodeQuiz

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

💡 Understanding Python Decorators

Decorators are a powerful feature in Python that allow you to add functionality to an existing function without modifying its source code. A decorator is essentially a function that takes another function as an argument, wraps it in an inner function (the "wrapper"), and returns the wrapper. This is useful for tasks like logging, timing, or access control.

import time

def timer_decorator(func):
    """A decorator that prints the execution time of a function."""
    def wrapper(*args, **kwargs):
        start_time = time.perf_counter()
        result = func(*args, **kwargs)
        end_time = time.perf_counter()
        run_time = end_time - start_time
        print(f"Finished {func.__name__!r} in {run_time:.4f} secs")
        return result
    return wrapper

@timer_decorator
def process_heavy_data(n):
    """A sample function that simulates a time-consuming task."""
    sum = 0
    for i in range(n):
        sum += i
    return sum

process_heavy_data(10000000)

Code explanation: The timer_decorator function takes process_heavy_data as its argument. The @timer_decorator syntax is shorthand for process_heavy_data = timer_decorator(process_heavy_data). When the decorated function is called, the wrapper inside the decorator executes, recording the start time, running the original function, recording the end time, and printing the duration.

#Python #Decorators #Programming #CodeTips #PythonTutorial

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

Python tip:

itertools.zip_longest pairs elements from multiple iterables, but unlike the built-in zip(), it continues until the longest iterable is exhausted, padding shorter ones with a specified fillvalue.

While zip() truncates its output to the length of the shortest input, zip_longest() ensures no data is lost from longer inputs by substituting None (or a custom value) for missing items.

Example👇

>>> import itertools
>>> students = ['Alice', 'Bob', 'Charlie', 'David']
>>> scores = [88, 92, 75]
>>> grades = list(itertools.zip_longest(students, scores, fillvalue='Absent'))
grades
[('Alice', 88), ('Bob', 92), ('Charlie', 75), ('David', 'Absent')]

#Python #ProgrammingTips #Itertools #PythonTips #CleanCode

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

Python Clean Code:

The collections.defaultdict simplifies dictionary creation by providing a default value for keys that have not been set yet, eliminating the need for manual existence checks.

Instead of writing if key not in my_dict: before initializing a value (like a list or a counter), defaultdict handles this logic automatically upon the first access of a missing key. This prevents KeyError and makes grouping and counting code significantly cleaner.

Example👇

>>> from collections import defaultdict
>>>
>>> # Cluttered way with a standard dict
>>> data = [('fruit', 'apple'), ('veg', 'carrot'), ('fruit', 'banana')]
>>> grouped_data = {}
>>> for category, item in data:
...     if category not in grouped_data:
...         grouped_data[category] = []
...     grouped_data[category].append(item)
...
>>> # Clean way with defaultdict
>>> clean_grouped_data = defaultdict(list)
>>> for category, item in data:
...     clean_grouped_data[category].append(item)
...
>>> clean_grouped_data
defaultdict(<class 'list'>, {'fruit': ['apple', 'banana'], 'veg': ['carrot']})

#Python #CleanCode #PythonTips #DataStructures #CodeReadability

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

Python Intermediate Level Certification Exam

Instructions:
This exam consists of 50 multiple-choice and code-output questions.
The suggested time for each question is indicated. Total Time: 60 Minutes.
• Choose the single best answer for each question.

---

Section 1: Core Concepts & Data Structures

• (Time: 60s) What will be the output of the following code?

x = 10
def outer_func():
    x = 20
    def inner_func():
        print(x)
    inner_func()
outer_func()

a) 10
b) 20
c) Error
d) None

• (Time: 75s) What is the output of this code?

my_list = [1, 2, [3, 4]]
new_list = my_list.copy()
new_list[2][0] = 99
print(my_list)

a) [1, 2, [3, 4]]
b) [1, 2, [99, 4]]
c) [99, 2, [3, 4]]
d) A TypeError occurs.

• (Time: 45s) Which of the following is true about the is and == operators?
a) is compares object identity, == compares value.
b) is compares value, == compares object identity.
c) They are completely interchangeable.
d) is is used for strings and == is for numbers.

• (Time: 60s) What will be the output?

nums = [1, 2, 3, 4, 5, 6]
print(nums[::-2])

a) [6, 4, 2]
b) [1, 3, 5]
c) [2, 4, 6]
d) [5, 3, 1]

• (Time: 75s) What is the most likely output of this code in CPython?

a = 256
b = 256
print(a is b)

c = 257
d = 257
print(c is d)

a) True, True
b) False, False
c) True, False
d) False, True

• (Time: 75s) Which of the following is the primary advantage of a generator expression over a list comprehension?
a) It is always faster.
b) It uses less memory because it evaluates lazily.
c) It can be indexed.
d) It can be used for both reading and writing data.

• (Time: 60s) What is the result of the following set operation?

s1 = {1, 2, 3, 4}
s2 = {3, 4, 5, 6}
print(s1 ^ s2)

a) {3, 4}
b) {1, 2, 5, 6}
c) {1, 2, 3, 4, 5, 6}
d) An error occurs.

• (Time: 90s) What will be printed?

def add_item(item, my_list=[]):
    my_list.append(item)
    return my_list

print(add_item(1))
print(add_item(2))
print(add_item(3, [10, 20]))

a) [1], [2], [10, 20, 3]
b) [1], [1, 2], [10, 20, 3]
c) [1], [2], [3]
d) [1], [1, 2], [1, 2, 3]

Section 2: Functions & Decorators

• (Time: 60s) What do *args and **kwargs represent in a function definition?
a) Required positional and keyword arguments.
b) Optional positional arguments and required keyword arguments.
c) Pointers to arguments.
d) A variable number of positional and keyword arguments.

• (Time: 75s) What is the output of this code?

def my_decorator(func):
    def wrapper():
        print("Before")
        func()
        print("After")
    return wrapper

@my_decorator
def say_hello():
    print("Hello!")

say_hello()

a) Hello!
b) Before, Hello!, After (each on a new line)
c) Before, After, Hello! (each on a new line)
d) An error occurs.

• (Time: 60s) Which statement about lambda functions is true?
a) They can contain multiple expressions.
b) They cannot have default arguments.
c) They are anonymous functions restricted to a single expression.
d) They must be assigned to a variable.

• (Time: 75s) What will be the output?

data = [1, 2, 3, 4, 5]
result = list(map(lambda x: x * 2, filter(lambda x: x % 2 == 0, data)))
print(result)

a) [2, 4, 6, 8, 10]
b) [4, 8]
c) [2, 4]
d) A TypeError occurs.

• (Time: 90s) What does the yield keyword do in a function?
a) It terminates the function immediately.
b) It returns a value and raises an exception.
c) It makes the function a generator, pausing its execution and returning a value.
d) It prints a value to the console without returning it.

• (Time: 60s) What will this code print?

def get_multiplier(n):
    def multiplier(x):
        return x * n
    return multiplier

double = get_multiplier(2)
print(double(5))

a) 2
b) 5
c) 10
d) An error occurs.

• (Time: 45s) The concept demonstrated in the question above is called a:
a) Decorator
b) Generator
c) Lambda
d) Closure

Section 3: Object-Oriented Programming (OOP)

• (Time: 60s) What is the main purpose of the __init__ method in a Python class?
a) To destroy an instance of a class.
b) To be the first method run when a script is executed.
c) To initialize the state of a new object when it is created.
d) To define a static method.

• (Time: 75s) What is the difference between a class variable and an instance variable?
a) They are the same.
b) Class variables are shared by all instances; instance variables are unique to each instance.
c) Instance variables are shared by all instances; class variables are unique to each instance.
d) Class variables cannot be modified.

• (Time: 90s) What will be the output of this code?

class Parent:
    def speak(self):
        print("Parent speaking")

class Child(Parent):
    def speak(self):
        print("Child speaking")
        super().speak()

c = Child()
c.speak()

a) Parent speaking
b) Child speaking
c) Child speaking, Parent speaking (on new lines)
d) Parent speaking, Child speaking (on new lines)

• (Time: 60s) What is the purpose of the super() function in OOP?
a) To call a method from a parent class.
b) To create a superclass.
c) To check if a class is a superclass of another.
d) To delete a parent class.

• (Time: 90s) What does the @staticmethod decorator do?
a) It creates a method that automatically receives the class as the first argument.
b) It converts a method into a function that belongs to the class but doesn't receive any implicit first argument (self or cls).
c) It makes a method private.
d) It allows a method to be called on an instance only.

• (Time: 75s) What is the correct way to define a property that makes an attribute read-only?
a) def get_my_attr(self): ...
b) __my_attr = value
c) @property def my_attr(self): ...
d) static my_attr = value

• (Time: 60s) The principle of a subclass providing a specific implementation of a method that is already provided by its parent class is called:
a) Inheritance
b) Encapsulation
c) Polymorphism
d) Abstraction

• (Time: 90s) What will len(p) return after running this code?

class Playlist:
    def __init__(self, songs):
        self.songs = songs
    
    def __len__(self):
        return len(self.songs)

p = Playlist(['Song A', 'Song B', 'Song C'])

a) An error, len() is not supported.
b) 0
c) 3
d) The memory address of the object.

Section 4: Modules & Standard Library

• (Time: 60s) What is the primary use of the collections.defaultdict?
a) To create a dictionary with a default sorting order.
b) To provide a default value for a key that does not exist, avoiding a KeyError.
c) To create a thread-safe dictionary.
d) To create a dictionary that remembers insertion order (like a standard dict in Python 3.7+).

• (Time: 75s) What will this code print?

from collections import Counter
my_list = ['a', 'b', 'c', 'a', 'b', 'a']
counts = Counter(my_list)
print(counts['a'])

a) 1
b) 2
c) 3
d) ['a', 'a', 'a']

• (Time: 60s) Which module is used for working with dates and times?
a) time
b) date
c) datetime
d) calendar

• (Time: 75s) How do you serialize a Python dictionary d into a JSON formatted string?
a) json.load(d)
b) json.dumps(d)
c) pickle.dumps(d)
d) d.to_json()

• (Time: 90s) What is the output?

import itertools

data = ['A', 'B']
result = list(itertools.permutations(data, 2))
print(result)

a) [('A', 'B')]
b) [('A', 'A'), ('A', 'B'), ('B', 'A'), ('B', 'B')]
c) [('A', 'B'), ('B', 'A')]
d) [('A',), ('B',)]

• (Time: 60s) To check if a file named data.txt exists in the current directory, you would use:
a) os.path.exists('data.txt')
b) os.file.exists('data.txt')
c) sys.path.exists('data.txt')
d) open('data.txt').exists()

Section 5: Error Handling & File I/O

• (Time: 45s) What is the primary purpose of a try...except block?
a) To speed up code execution.
b) To handle exceptions and prevent the program from crashing.
c) To define a new type of error.
d) To test code for syntax errors.

• (Time: 90s) In a try...except...else...finally block, which block is always executed regardless of whether an exception occurred or not?
a) try
b) except
c) else
d) finally

• (Time: 75s) Why is using the with statement (with open(...) as f:) preferred for file handling?
a) It automatically reads the entire file into memory.
b) It provides better performance than a standard try...finally.
c) It automatically closes the file, even if exceptions occur.
d) It automatically creates the file if it doesn't exist.

• (Time: 60s) Which is the best practice for catching exceptions?
a) except:
b) except Exception:
c) except ValueError: (or another specific exception)
d) except Error:

• (Time: 75s) What will happen when this code runs?

try:
    print("Start")
    result = 10 / 0
    print("Middle")
except ZeroDivisionError:
    print("Caught error")
else:
    print("No error")
finally:
    print("Finish")

a) Start, Middle, No error, Finish
b) Start, Caught error, Finish
c) Start, Caught error, No error, Finish
d) The program will crash.

Section 6: Advanced & Miscellaneous

• (Time: 75s) What does a list comprehension like [x for x in range(10) if x % 2 == 0] do?
a) Creates a list of booleans.
b) Creates a list of even numbers from 0 to 9.
c) Creates a list of odd numbers from 0 to 9.
d) Returns a generator object.

• (Time: 90s) What is the output?

my_dict = {'a': 1, 'b': 2, 'c': 3}
new_dict = {k:v for v, k in my_dict.items()}
print(new_dict[1])

a) a
b) 1
c) b
d) A KeyError.

• (Time: 75s) What is the Global Interpreter Lock (GIL) in CPython?
a) A security feature that locks global variables.
b) A mechanism that allows only one thread to execute Python bytecode at a time.
c) A tool for debugging multi-threaded applications.
d) A memory management utility.

• (Time: 60s) What is the result of *a, b = [1, 2, 3, 4, 5]?
a) a is [1, 2, 3, 4] and b is 5.
b) a is 1 and b is [2, 3, 4, 5].
c) It raises a SyntaxError.
d) a is (1, 2, 3, 4) and b is 5.

• (Time: 90s) To implement a context manager, a class must have which methods?
a) __init__ and __del__
b) __enter__ and __exit__
c) __open__ and __close__
d) __start__ and __stop__

• (Time: 60s) What will the sorted() function do with this input? sorted(['c', 'B', 'a'])
a) ['a', 'B', 'c'] (case-insensitive sort)
b) ['a', 'c', 'B']
c) ['B', 'a', 'c'] (ASCII order)
d) ['c', 'B', 'a'] (no change)

• (Time: 75s) Which of the following correctly finds all numbers greater than 10 in a list called nums in a functional style?
a) filter(lambda x: x > 10, nums)
b) [x for x in nums if x > 10]
c) map(lambda x: x > 10, nums)
d) reduce(lambda x, y: x > 10, nums)

• (Time: 60s) A module that is imported is executed:
a) Every time it is imported.
b) Only the first time it is imported in a program's execution.
c) Never, its functions are just made available.
d) Every time a function from it is called.

• (Time: 90s) What is the purpose of the if __name__ == "__main__": block?
a) It defines the main function of the script.
b) It is required for all Python scripts to run.
c) It allows code to be run only when the file is executed directly as a script, not when imported.
d) It is a comment and has no effect on execution.

• (Time: 60s) Which data type is mutable?
a) tuple
b) str
c) int
d) dict

• (Time: 75s) What is printed?

t = (1, 2, [3, 4])
try:
    t[1] = 5
except TypeError:
    print("Error 1")
try:
    t[2][0] = 9
except TypeError:
    print("Error 2")
print(t)

a) Error 1, (1, 2, [9, 4])
b) Error 1, Error 2, (1, 2, [3, 4])
c) Error 1, (1, 2, [3, 4])
d) The code does not print anything.

• (Time: 45s) What is a "virtual environment" in Python used for?
a) To run Python code on a virtual machine.
b) To create an isolated environment for a project with its own dependencies.
c) To test code performance in different environments.
d) A built-in Python IDE.

• (Time: 75s) What is the output?

from collections import namedtuple
Point = namedtuple('Point', 'x y')
p = Point(10, 20)
print(p.x + p[1])

a) A TypeError.
b) 20
c) 30
d) 10

• (Time: 60s) How would you remove the key 'b' from the dictionary d = {'a': 1, 'b': 2}?
a) d.remove('b')
b) d.pop('b')
c) d.delete('b')
d) d.slice('b')

• (Time: 90s) Consider the Walrus Operator :=. What will be the value of count after this loop?

data = [1, 2, 3, 4]
count = 0
while (n := len(data)) > 2:
    data.pop()
    count += n

a) 0
b) 2
c) 7
d) 9

• (Time: 60s) What does the pass statement do?
a) It terminates the program.
b) It skips the current iteration of a loop.
c) It is a null operation; nothing happens when it executes.
d) It raises a NotImplementedError.

#Python #Certification #Exam #Programming #CodingTest #Intermediate

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

🏆 Mastering 150 JavaScript Core Instructions

📢 Dive into JavaScript! Our comprehensive guide covers 150 core instructions, definitions, and use cases with practical examples. Master web development essentials!

⚡ Tap to unlock the complete answer and gain instant insight.

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨

🏆 150 Design Patterns: A Complete Guide

📢 Dive into 150 essential Software Design Patterns! Learn practical solutions with code examples to build robust, efficient, and scalable applications.

⚡ Tap to unlock the complete answer and gain instant insight.

━━━━━━━━━━━━━━━
By: @DataScienceQ ✨