We have some service that writes data continuously to the disc. The service can use one of the three storage backends, 20 megabytes per second minimum 80 megabytes and 250 megabytes. We need to perform a right speed test on slash data folder to measure this throughput. The tool that we'll use called...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

We had some incident where massive amount logs were written in var log, app access log. This file will be very large in gigabytes and it'll be very difficult to use any editor or analytics tool to analyze this file. The question asks us to split this file into smaller, manageable chunks. First, create folder TMP log parts. We'll use WC word count, and then lines var log, app access log, and it contains 375 lines. We are going to use split command to split this file into more manageable chunks. We'll use hyphen l flag to split it in smaller chunks because by default, split command does a split into 1000 lines. Since question is asking us to split that in a hundred lines, we'll use hyphen L hundred and then we add file name var log, app access log, and the destination where we like to save our chunks.

def max_area(height: list[int]) -> int:
l, r = 0, len(height) - 1
res = 0

while l < r:
    area = (r - l) * min(height[l], height[r])
    res = max(res, area)
    
    if height[l] < height[r]:
        l += 1
    else:
        r -= 1
        
return res

Use regular expressions to extract timestamps from log entries in various formats (ISO 8601, Apache, RFC 2822), parse them using Python datetime, and convert all timestamps to a uniform ISO 8601 format.

Definition for singly-linked list.

class ListNode:

def init(self, val=0, next=None):

self.val = val

self.next = next

def merge_k_lists(lists: list[Optional[ListNode]]) -> Optional[ListNode]:
min_heap = []

for i, l in enumerate(lists):
    if l:
        heapq.heappush(min_heap, (l.val, i, l))
        
dummy = ListNode(0)
tail = dummy

while min_heap:
    val, i, node = heapq.heappop(min_heap)
    tail.next = node
    tail = tail.next
    
    if node.next:
        heapq.heappush(min_heap, (node.next.val, i, node.next))
        
return dummy.next

Set up an AWS account-wide IAM password policy to enforce minimum length, character requirements, and prevent password reuse using AWS CLI.

def walls_and_gates(rooms: list[list[int]]) -> list[list[int]]:
if not rooms or not rooms[0]:
return rooms

rows, cols = len(rooms), len(rooms[0])
q = deque()
INF = 2147483647

for r in range(rows):
    for c in range(cols):
        if rooms[r][c] == 0:
            q.append((r, c))
            
directions = [[1, 0], [-1, 0], [0, 1], [0, -1]]

while q:
    r, c = q.popleft()
    
    for dr, dc in directions:
        nr, nc = r + dr, c + dc
        if 0 <= nr < rows and 0 <= nc < cols and rooms[nr][nc] == INF:
            rooms[nr][nc] = rooms[r][c] + 1
            q.append((nr, nc))
            
return rooms

This question is about user app deployer, having way too much access and basically having root access administrator access policy on our AWS account, this violates strategy of lease privilege, meaning user should only have access to the resources that he needs and nothing more. This user should have access to S3 read, add Object and lease buckets and CloudWatch logs to create log groups, log streams, and put log events. Our task is to inspect current policies attached to app deployer. Remove overly broad administrative access policy, create new policy with proper rights, and attach it to the user app deployer. In AWS, administrator access is building policy that has maximum access on AWS account. The second item is action. Action means what type of action we can perform on AWS. Resource means to which AWS resource this action could apply. Wildcard means that we can put AWS S3 object to any S3 bucket. We need to first inspect current policies attached to the app Deployer user. We've been asked in S3 to get object, put Object and lease buckets. Same for logs. Create log group, create log stream, and put log events.

Create an ECR repository with tag immutability, a lifecycle policy to expire old untagged images, and a repository policy granting cross-account pull access.

We need to build an internal serverless API for an order management. Service orders will go into DynamoDB table and all access to that table must go through a Lambda function. Lambda is going to be our handler and we will not access DynamoDB directly. Lambda execution role should only have the exact permission it needs and nothing more. This means that it's gonna be a least privileged role and functionality of the app needs to read specific order, never scan entire database, and write orders into DynamoDB. An IAM role gives our lambda function a temporary access credentials to be able to perform actions on our AWS services. In order to call our lambda function, we need some handler. API gateway gives us a straightforward solution to call Lambda function. We'll create two methods, get and post in our API gateway and point it to our Lambda function. First thing we need to do is to create our DynamoDB table. The name will be orders and partition key will be order id. It's been said in the question that we are not allowed to attach wildcard policies. So we'll create inline policy. The resource will be our DynamoDB table. This will be put item and get item in DynamoDB.

Deploy a highly available containerized application on ECS Fargate with RDS, ALB, ECR, and Secrets Manager across multiple Availability Zones with proper network isolation and security groups.

Build an event-driven architecture using EventBridge, SQS, SNS, and CloudWatch to fan out order events to multiple independent consumers with dead-letter handling and least-privilege access.

Implement a blue/green deployment strategy using Auto Scaling Groups, ALB weighted target groups, launch templates, and Secrets Manager for zero-downtime deployments with instant rollback capability.

Configure secure cross-VPC connectivity using VPC Peering so that an ECS Fargate application in one VPC can access a private RDS database in another VPC with proper routing, DNS resolution, and security group controls.

Test your enterprise networking skills by designing a secure, hub-and-spoke AWS Transit Gateway architecture with strict traffic segmentation and centralized VPC Flow Logs.

Mastering the Interview Question:

Counting Employee Hires per Month by Department in SQL

Objective

To tackle this SQL interview question, we're given two tables: employees and departments. Our goal is to determine the number of employees hired each month for every department.

E...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

The main focus is on ranking values with dense rank window function. We are working with sales table that tracks individual sales made by different representatives. The goal here is to first add up all sales per person to get their total value, and then rank everyone based on that total from highest to lowest. When we use dense ranking, two people with equal amount of sales will share the same rank. A CTE is a temporary result set in SQL that you can reference within a single query. Dense rank is one of the window functions that assigns a rank number to each row based on a specified order. Over is a keyword that simply activates a window function and lets SQL know that we try to implement it. Finally, order by clause sorts by sales rank first, so rank one comes before rank two, and then salesperson name is sorted alphabetically.

How to Tackle the SQL Interview Question: Summarize Online and Offline Orders

When interviewing for a position that involves working with SQL, one common question you might encounter is:

Objective

Create a SQL query that retrieves a summary of orders from two separate sources: online and ...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Objective

Write an SQL query that retrieves the customer name, order date, payment amount, and payment date for all payments greater than $1000. The results should be sorted in descending order of payment amount and, for payments with the same amount, by ascending order date.

Additional inf...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Convert nested JSON objects to flat CSV format using dot-notation for nested fields and comma-separated strings for arrays with pandas in Python.

SELECT
date,
COUNT(DISTINCT cust_id) AS num_customers,
SUM(duration) AS total_duration
FROM {{ ref("calls") }}
GROUP BY date

SELECT
sample_id,
description,
COALESCE(REGEXP_SUBSTR(description, '[0-9]+'), '') AS age
FROM {{ ref("rock_samples") }}

Join products with orders and compute per-category average price and order count.

Spark is a framework that processes large amounts of data across multiple machines. Instead of tables, it uses data frames. We are given three files, and their data frames are called products, sales, and inventory. We need to calculate total quantity sold and total revenue per product from sales. Then we find the total stock number per product across all warehouses. And we need to join everything to products data frame and make sure every product appears, even if it has no sales or inventory records. Any null values should be replaced with zero. We group by product ID so that all sales rows that belong to the same product are put together. Inner join returns only rows where a match exist in both tables or data frames. Left outer join returns everything from the left table, plus matching rows from the right. In our specific case, we'll use left join. It will keep all rows from the left table, which is products, regardless whether there is a match in sales. Coalesce function takes list of values and returns the first one that is not null. Lit function creates a literal value of zero.

Master advanced PySpark Window functions by calculating both partition-level and global maximums. Learn how to use empty window partitions and conditional F.when() logic to identify top-performing SEO pages.

Practice cleaning and merging manufacturing data in PySpark. Learn how to remove exact duplicate rows using dropDuplicates() before joining multiple DataFrames together.

SELECT
id,
name,
city,
country,
CASE
WHEN floors != 0
THEN ROUND(height_m / floors, 2)
ELSE 0
END AS avg_height_per_floor
FROM {{ ref("buildings") }}

Identifying Products for Reorder in Inventory Using SQL

Objective

In this task, you'll write an SQL query to identify products in the inventory that need to be reordered. For each product that requires reordering, you'll calculate the suggested order quantity with the formula: `CEIL((reorder...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

from collections import defaultdict, OrderedDict

class LFUCache:
def init(self, capacity):
self.cap = capacity
self.min_freq = 0
self.key_val = {}
self.key_freq = {}
self.freq_keys = defaultdict(OrderedDict)

def _update(self, key):
    freq = self.key_freq[key]
    self.freq_keys[freq].pop(key)
    if not self.freq_keys[freq] and self.min_freq == freq:
        self.min_freq += 1
    self.key_freq[key] = freq + 1
    self.freq_keys[freq + 1][key] = None

def get(self, key):
    if key not in self.key_val:
        return -1
    self._update(key)
    return self.key_val[key]

def put(self, key, value):
    if self.cap <= 0:
        return
    if key in self.key_val:
        self.key_val[key] = value
        self._update(key)
        return
    if len(self.key_val) >= self.cap:
        evict_key, _ = self.freq_keys[self.min_freq].popitem(last=False)
        del self.key_val[evict_key]
        del self.key_freq[evict_key]
    self.key_val[key] = value
    self.key_freq[key] = 1
    self.freq_keys[1][key] = None
    self.min_freq = 1

from collections import deque

class MovingAverage:
def init(self, size):
self.size = size
self.queue = deque()
self.window_sum = 0

def next(self, val):
    self.queue.append(val)
    self.window_sum += val
    if len(self.queue) > self.size:
        self.window_sum -= self.queue.popleft()
    return self.window_sum / len(self.queue)

How to Calculate the Return Rate of Orders for Each Product Category Using SQL

Objective

In a SQL interview, you might encounter a challenging yet intriguing question: Write a SQL query to calculate the return rate of orders for each product category. The return rate is defined as the per...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

SELECT
DATE_TRUNC('month', review_date) AS review_month,
product_id,
ROUND(AVG(star_rating)) AS avg_rating
FROM
product_reviews
GROUP BY
DATE_TRUNC('month', review_date),
product_id
ORDER BY
review_month ASC,
product_id ASC;

SELECT
ROUND(
SUM(item_count * order_occurrences)::DECIMAL / SUM(order_occurrences),
1
) AS mean_items
FROM
items_per_order;

SELECT
COUNT(policy_holder_id) AS policy_holder_count
FROM
(
SELECT
policy_holder_id,
COUNT(case_id) AS call_count
FROM
callers
GROUP BY
policy_holder_id
HAVING
COUNT(case_id) >= 3
) AS frequent_callers;

SELECT
MAX(salary) AS second_highest_salary
FROM
employee
WHERE
salary < (
SELECT
MAX(salary)
FROM
employee
);

SELECT
category,
product,
total_spend
FROM
(
SELECT
category,
product,
SUM(spend) AS total_spend,
RANK() OVER (
PARTITION BY
category
ORDER BY
SUM(spend) DESC
) AS ranking
FROM
product_spend
WHERE
EXTRACT(
YEAR
FROM
transaction_date
) = 2022
GROUP BY
category,
product
) AS ranked_products
WHERE
ranking <= 2
ORDER BY
category,
total_spend DESC;

SELECT
cp.customer_id
FROM
customer_purchases cp
JOIN product_catalog pc ON cp.product_id = pc.product_id
GROUP BY
cp.customer_id
HAVING
COUNT(DISTINCT pc.category) = (
SELECT
COUNT(DISTINCT category)
FROM
product_catalog
)
ORDER BY
cp.customer_id;

WITH
summary AS (
SELECT
item_type,
SUM(item_count) AS batch_count,
SUM(square_footage * item_count) AS total_sqft
FROM
inventory
GROUP BY
item_type
)
SELECT
item_type,
CASE
WHEN item_type = 'prime_eligible' THEN batch_count
ELSE FLOOR(
(
500000 - (
SELECT
total_sqft
FROM
summary
WHERE
item_type = 'prime_eligible'
)
) * batch_count::DECIMAL / total_sqft
)
END AS max_batch_count
FROM
summary
ORDER BY
item_type DESC;

WITH
managers AS (
SELECT DISTINCT
manager_id AS employee_id
FROM
employees
WHERE
manager_id IS NOT NULL
),
manages_managers AS (
SELECT DISTINCT
e.manager_id AS employee_id
FROM
employees e
JOIN managers m ON e.employee_id = m.employee_id
WHERE
e.manager_id IS NOT NULL
)
SELECT
mgr.employee_name AS senior_manager_name,
COUNT(*) AS direct_report_count
FROM
manages_managers mm
JOIN employees mgr ON mm.employee_id = mgr.employee_id
JOIN employees e ON e.manager_id = mm.employee_id
WHERE
NOT EXISTS (
SELECT
1
FROM
employees sub
JOIN manages_managers mm2 ON sub.employee_id = mm2.employee_id
WHERE
sub.manager_id = mm.employee_id
)
GROUP BY
mgr.employee_name
ORDER BY
direct_report_count DESC,
senior_manager_name;

WITH
sessions AS (
SELECT
server_id,
status_time,
session_status,
LEAD(status_time) OVER (
PARTITION BY
server_id
ORDER BY
status_time
) AS stop_time
FROM
server_utilization
)
SELECT
FLOOR(
SUM(
EXTRACT(
EPOCH
FROM
(stop_time - status_time)
)
) / 86400
)::INT AS total_uptime_days
FROM
sessions
WHERE
session_status = 'start';

Understanding the SQL Query to Identify High-Impact Promotional Campaigns

When tackling an interview question about SQL queries for identifying influential promotional campaigns, there are several key components you need to consider.

Objective

Your task is to write a SQL query that identi...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Apple's ecosystem connects over 2 billion active devices worldwide across iPhone, iPad, Mac, Apple Watch, and Apple TV. QA testing of Apple device ecosystem APIs requires comprehensive validation of device registration, sync services, iCloud backup management, and cross-platform data sharing to ensu...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Master simple button click testing with Selenium. Learn basic element interaction and click verification for beginners....

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →