Deutschebank Interview Questions (5+ Questions)

Fix No space left on device errors on Linux when disk space is available but inodes are exhausted. Learn how to diagnose inode exhaustion using df -i, identify directories with millions of small files, trace which application is creating them, and safely clean up problem paths to restore normal system operation. This guide covers inode basics, detecting inode usage bottlenecks, scanning for high-file-count directories, and verifying recovery by freeing inodes and re-enabling file creation. Essential for troubleshooting production outages where logs, applications, or package installs suddenly fail despite plenty of free disk space.

Understanding the 7-Day Moving Average SQL Query

In the realm of data analysis and business intelligence, computing moving averages is a common task. A moving average helps smooth out data over a specified number of periods, highlighting trends over time. For this interview question, you'll be working with a table named sales, containing two columns: sale_date and amount. Your objective is to calculate the 7-day moving average of amount for each sale_date.

Key Requirements

1. Table and Columns: You have a table called sales with sale_date (in YYYY-MM-DD format) and amount.
2. Moving Average Calculation: Calculate the 7-day moving average of amount, including the current day and the six preceding days.
3. Output Columns: The resulting query should include sale_date, amount, and the computed moving_average.
4. Output Order: Results should be ordered by sale_date in ascending order.
5. SQL Window Functions: Utilize SQL window functions to optimize the moving average calculation.
6. Assumptions: Assume no duplicate sale_date entries and that sale_date values are consecutive without gaps.

SQL Query for 7-Day Moving Average

To achieve this, you can use SQL window functions, particularly the AVG() function over a specified window of rows. Here's how you can write the SQL query:

SELECT 
    sale_date,
    amount,
    ROUND(AVG(amount) OVER (
        ORDER BY sale_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS moving_average
FROM 
    sales
ORDER BY 
    sale_date ASC;

Explanation

• SELECT Clause: Choose sale_date, amount, and the computed moving_average.
• ROUND Function: Round the moving average to two decimal places for better readability.
• AVG() Window Function: Calculate the average of amount over a window of 7 days.
  • ORDER BY sale_date: Dictates the order in which the data is processed.
  • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW: Defines the window of 7 days, including the current row and the six preceding rows.
• ORDER BY Clause: Finally, sort the results by sale_date in ascending order for chronological coherence.

SEO Keywords and Phrases

• SQL Window Functions
• 7-Day Moving Average in SQL
• SQL Query for Moving Average
• SQL AVG() Function
• Data Analysis with SQL
• Calculating Moving Averages
• SQL for Business Intelligence
• Time-series Data in SQL

Conclusion

By utilizing SQL window functions, specifically the AVG() function with a defined window, you can efficiently calculate the 7-day moving average for each sale_date in the sales table. This approach ensures that you meet the outlined requirements, providing an accurate and neatly formatted output, essential for data analysis tasks.

How to Calculate the Engagement Rate of Social Media Posts Using SQL

Objective

In this article, we will guide you through creating an SQL query to determine the engagement rate of social media posts. The engagement rate is a crucial metric for understanding how well content performs on soc...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Certainly! Here is a detailed, SEO-friendly explanation for the given interview question.

Retrieve Comprehensive Customer Information with SQL: An Essential Guide

Objective

Develop an SQL query aimed at extracting extensive customer information from the combined_customers table. T...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →

Detailed Explanation of Interview Question: Calculating Monthly Application-to-Interview and Interview-to-Hire Rates Using SQL

Objective

To tackle this SQL query interview question, you'll be required to compute two key metrics from a job applications table: the application-to-interview ra...

🔒 Premium Content

Detailed explanation and solution available for premium members.

Upgrade to Premium →