Katkat
Back

Write a quarterly SQL report and a bash max-file script

OthersSoftware Engineer · Online Assessment
MediumSoftware EngineerOnline AssessmentSoftware Engineering Fundamentals

Last updated

This problem tests your ability to perform conditional aggregation in SQL for time-series reporting and your proficiency in recursive shell scripting for system administration tasks.

Part 1: SQL Reporting

Assume a table sales with columns sale_id (int), amount (decimal), and sale_date (datetime). Write a SQL query to generate a report showing the total sales amount for each quarter of the year 2023. The output should include columns: quarter (1-4) and total_revenue.

Example Output:

quartertotal_revenue
15000.00
27200.50

Part 2: Bash Scripting

Write a bash script that takes a directory path as an argument. The script should recursively traverse the directory and output the name and size of the single largest file found. If the directory is empty or does not exist, provide a descriptive error message to stderr and exit with a non-zero status code.

Constraints:

  • For SQL, assume standard PostgreSQL or MySQL syntax.
  • For Bash, use standard utilities like find, du, or sort.
  • Ensure the bash script handles filenames with spaces correctly.

Hints

Hint 1

For the SQL part, use the `QUARTER()` function or `EXTRACT(QUARTER FROM ...)` combined with a `GROUP BY` clause.

Hint 2

For the bash script, use `find . -type f -exec du -h {} +` to list files and their sizes, then pipe the output to `sort -rh` to find the largest entry.

Hint 3

Remember to quote variables in bash (e.g., "$1") to handle paths or filenames that contain whitespace.

Follow-up questions

  • How would you modify the SQL query to include quarters with zero sales?
  • How could the bash script be optimized to handle directories with millions of files where `find` might consume too much memory?
  • How would you extend the bash script to exclude specific file extensions, such as '.tmp' files, from the search?