Carry forward non-null value with a windowing function? In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    NULL
John     1/5    NULL
John     1/6    NULL
John     1/7    18
Mary     1/2    35
Mary     1/3    NULL
Mary     1/4    -15
Mary     1/6    NULL
Mary     1/7    0


Is there a way to do something like:

SELECT
    [name],
    [date],
    CARRY([happiness]) OVER (PARTITION BY [name] ORDER BY [date])


So that the intermediate results look like:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    20
John     1/5    20
John     1/6    20
John     1/7    18
Mary     1/2    35
Mary     1/3    35
Mary     1/4    -15
Mary     1/6    -15
Mary     1/7    0


CARRY isn't a real function clearly, just want that functionality.
You can use a recursive CTE and COALESCE to return the most recent non-NULL value for Happiness. See this fiddle for a working example.

In the example, I am first using a CTE to create a pseudo ID column for use in the recursive CTE. Then in the recursive CTE, we select the ID = 1 row (the earliest row) as the anchor and recursively select the next row by ID for each name group. The COALESCE function picks the previous value if the current value is NULL for happiness.

Query:

;WITH DataC AS(
  SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS ID,
    [name],
    [date],
    [happiness]
  FROM Data
), CTE AS (
  SELECT ID,
    [name],
    [date],
    [happiness]
  FROM DataC
  WHERE ID = 1
  UNION ALL
  SELECT d.ID,
    d.[name],
    d.[date],
    COALESCE(d.[happiness], c.happiness) AS happiness
  FROM DataC d
  INNER JOIN CTE c ON c.Name = d.Name AND c.ID = d.ID - 1
)

SELECT * 
FROM CTE
ORDER BY name, date


Result: 

ID  name    date                        happiness
-------------------------------------------------
1   John    2020-01-03 00:00:00.000     20
2   John    2020-01-04 00:00:00.000     20
3   John    2020-01-05 00:00:00.000     20
4   John    2020-01-06 00:00:00.000     20
5   John    2020-01-07 00:00:00.000     18
1   Mary    2020-01-01 00:00:00.000     35
2   Mary    2020-01-02 00:00:00.000     35
3   Mary    2020-01-03 00:00:00.000     -15
4   Mary    2020-01-04 00:00:00.000     -15
5   Mary    2020-01-05 00:00:00.000     0