# Ship It or Skip It

> The calendar doesn't lie. How aggressive is this team, really?

Canonical URL: <https://datadriven.io/problems/ship_it_or_skip_it>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

How many deploys happened on each calendar day? List the days from earliest to latest.

## Worked solution and explanation

### What this problem really is

This is a plain date-bucketing aggregation wearing a deployment-velocity costume. The whole problem hinges on one move: collapse each timestamp to its calendar day before counting. The trap is reaching for EXTRACT(DOW FROM deploy_at), which buckets by day-of-week (Monday, Tuesday) instead of by date, silently merging every Monday across every year into a single row. Get that wrong and your '184 days' become 7 rows.

---

### Break down the requirements

#### Step 1: Extract the date

Truncate the deploy_at timestamp to its calendar day with DATE(deploy_at) so two deploys on the same date land in the same bucket regardless of the time-of-day.

#### Step 2: Count per day, then order

Count the rows in each day's bucket with COUNT(*), then sort by the date itself so the result reads earliest day to latest.

---

### The solution

**Date-grouped count**

```sql
SELECT DATE(deploy_at) AS deploy_date,
       COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY DATE(deploy_at)
ORDER BY deploy_date
```

> **Cost Analysis**
>
> A single sequential scan of 600K rows feeds a grouped count; no join, no subquery, no sort beyond the cheap final order on a low-cardinality date key. Output is one row per day that had a deploy.

> **Interviewers Watch For**
>
> The tell of seniority here is restraint. This is a two-line query body. Candidates who reach for window functions, self-joins, or a date dimension table on an unprompted daily count are signaling they over-build.

> **Common Pitfall**
>
> EXTRACT(DOW FROM deploy_at) returns the day-of-week (0 to 6), not the calendar date, so it collapses years of deploys into seven buckets. Use DATE(deploy_at) or DATE_TRUNC('day', deploy_at) to keep distinct calendar days.

---

## Common follow-up questions

- How would you include days that had zero deployments? _(Tests calendar table join or generate_series for gap-filling.)_
- How would you show the running total of deployments over time? _(Add SUM(COUNT(*)) OVER (ORDER BY day) as a window function.)_
- What changes if deploy_at is stored as a Unix epoch integer instead of a timestamp? _(Tests TO_TIMESTAMP conversion before date extraction.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/ship_it_or_skip_it)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.