Databricks-Spark-Assoc Using Spark SQL Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  SELECT * FROM events WHERE date_format(event_time, 'yyyy-MM-dd') >= date_sub(current_date(), 7)

  Why it's wrong here

  This uses date_format to extract only the date part of the string, discarding the time. It then compares that date string to date_sub(current_date(), 7), which returns a date. The comparison is between a string and a date, leading to implicit casting that may work but ignores the time-of-day, so events from exactly 7 days ago at any time are included, which may not match 'last 7 days' precisely. It also fails to handle the full timestamp comparison.

• ✓

  SELECT * FROM events WHERE to_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss') >= current_timestamp() - INTERVAL 7 DAYS

  Why this is correct

  This expression uses to_timestamp with the correct format pattern to convert the string column into a timestamp, then compares it against current_timestamp() minus a 7-day interval. Spark SQL supports INTERVAL 7 DAYS syntax, and this correctly filters rows within the last week. It is the only option that both parses the string format correctly and uses a valid interval expression.

• ✗

  SELECT * FROM events WHERE event_time >= current_date() - 7

  Why it's wrong here

  This compares a string column directly to a date value, which Spark SQL will attempt to implicitly cast. However, the string format 'yyyy-MM-dd HH:mm:ss' will not reliably cast to a date, and subtracting 7 from a date yields a date, not a timestamp, causing type mismatch or incorrect results. It also ignores the time component, so rows from 7 days ago may be incorrectly included or excluded.

• ✗

  SELECT * FROM events WHERE unix_timestamp(event_time) >= unix_timestamp(current_timestamp()) - 604800

  Why it's wrong here

  While unix_timestamp can parse strings, it expects the default format 'yyyy-MM-dd HH:mm:ss' only if the string matches exactly. Here it does, but the expression subtracts 604800 seconds (7 days) from the current Unix timestamp. This is technically correct in logic, but unix_timestamp returns seconds since epoch, and comparing integers works. However, the question asks for a Spark SQL expression that correctly filters, and this one is valid but less idiomatic. The real issue is that unix_timestamp may return null for malformed strings, silently dropping rows, whereas to_timestamp is more robust with explicit format.