Keep first record in group and populate rest with Null/0 in SQL?

Question

I have the following table in my database:

       date sales
1  2010-12-13    10
2  2010-12-13    10
3  2010-12-13    10
4  2010-12-13    10
5  2010-12-13    10
6  2010-12-14    20
7  2010-12-14    20
8  2010-12-14    20
9  2010-12-14    20
10 2010-12-14    20

Is there a way to attain the first record only and populate the rest with NULL or 0 for the remainder of the group? AS the grouping will be done by date and sales:

For example the intended output is:

         date sales
1  2010-12-13    10
2  2010-12-13    0
3  2010-12-13    0
4  2010-12-13    0
5  2010-12-13    0
6  2010-12-14    20
7  2010-12-14    0
8  2010-12-14    0
9  2010-12-14    0
10 2010-12-14    0

So essentially to keep the first record but make the rest of the records in the group be 0 (maybe Null if that is quicker/easier)

The closest i have got to solving this is attaining just the first record through an inner join - but I think a partition over may solve it - just stuck at the moment!

Any help appreciated!

Using SQLite - but also GCP (SQL) is accesible to me

Answer 1

This might work in SQLite:

CASE WHEN id = MIN(id) OVER(PARTITION BY date) THEN sales ELSE 0 END as sales

If it doesn't you can prepare a subquery that has only the min ID per date and join it in:

SELECT
   CASE WHEN y.id IS NULL THEN 0 ELSE sales END as sales
FROM
  x
  LEFT JOIN (SELECT MIN(id) as id FROM x GROUP BY date) y ON x.id= y.id