Write a query to find the total number of rides per driver in the last 30 days.

To find the total number of rides per driver in the last 30 days, you need to filter the rides table by ride_date and then aggregate the count of rides for each driver_id.

SELECT
    driver_id,
    COUNT(ride_id) AS ride_count -- Using ride_id for explicit count of distinct rides
FROM
    rides
WHERE
    ride_date >= CURRENT_DATE - INTERVAL '30' DAY -- Standard SQL for date subtraction
    AND status = 'completed' -- Focus on completed rides for meaningful metrics
GROUP BY
    driver_id
ORDER BY
    ride_count DESC;

Mechanics and Why

This query first filters the rides table to include only records from the last 30 days using ride_date >= CURRENT_DATE - INTERVAL '30' DAY. The INTERVAL syntax is standard SQL, though some databases might use DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) (MySQL) or CURRENT_DATE - 30 (PostgreSQL/Snowflake). We also add AND status = 'completed' to ensure we're counting actual, successful rides, which is crucial for most business metrics. After filtering, GROUP BY driver_id aggregates the results, and COUNT(ride_id) (or COUNT(*)) tallies the number of rides for each driver. Finally, ORDER BY ride_count DESC sorts the drivers by their ride volume.

Production Considerations and Trade-offs

In a production environment, consider the following:
* Data Type and Precision: Ensure ride_date is indexed and appropriately typed (e.g., DATE or TIMESTAMP). If it's a TIMESTAMP, you might need CAST(ride_timestamp AS DATE) or DATE(ride_timestamp) for accurate daily comparisons.
* Performance: For large tables, an index on (ride_date, driver_id, status) is critical for efficient filtering and grouping. If the rides table is partitioned by ride_date (common in data warehouses like Snowflake or Spark), the WHERE clause will enable partition pruning, significantly reducing the data scanned.
* Ride Status: Always clarify what constitutes a "ride." status = 'completed' is a common requirement to exclude cancelled, pending, or failed rides.
* Data Freshness: CURRENT_DATE (or GETDATE(), NOW()) reflects the execution time. If this query runs daily, it consistently provides a 30-day rolling window.

In the interview, also mention…

Discuss how this metric could be used (e.g., driver performance analysis, incentive programs, capacity planning) and the importance of data quality checks on driver_id and ride_date to ensure accurate results.