The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 24): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `day` is indexed, the index won’t be used as it’s wrapped with the function `dateadd`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 24): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `starts_on` is indexed, the index won’t be used as it’s wrapped with the function `dateadd`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Correlated Subqueries (query line: 18): A correlated subquery is a subquery that contains a reference (column: starts_on_j) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
4. Avoid Correlated Subqueries (query line: 48): A correlated subquery is a subquery that contains a reference (column: ends_on) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
5. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
6. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
7. Use UNION ALL instead of UNION (query line: 35): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

Optimal indexes for this query:

CREATE INDEX events_idx_ends_on ON events (ends_on);
CREATE INDEX prices_idx_for_date ON prices (for_date);

The optimized query:

SELECT
        sp_for_date,
        sp_value 
    FROM
        ((SELECT
            sp.for_date AS sp_for_date,
            sp.value AS sp_value 
        FROM
            prices sp 
        INNER JOIN
            events ev 
                ON (
                    (
                        (
                            ev.ends_on IS NULL 
                            AND (
                                sp.for_date = (
                                    SELECT
                                        prices.for_date 
                                FROM
                                    prices 
                                WHERE
                                    prices.for_date <= ev.starts_on_j 
                                    AND prices.for_date > dateadd(prices.day, -14, ev.starts_on) 
                                ORDER BY
                                    prices.for_date DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
                            )
                        )
                    )
                )
            )
        ) 
    UNION
    (
        SELECT
            sp.for_date AS sp_for_date,
            sp.value AS sp_value 
        FROM
            prices sp 
        INNER JOIN
            events ev 
                ON (
                    (
                        (
                            ev.ends_on IS NOT NULL 
                            AND (
                                sp.for_date = (
                                    SELECT
                                        prices.for_date 
                                FROM
                                    prices 
                                WHERE
                                    prices.for_date <= ev.ends_on 
                                    AND prices.for_date > ev.starts_on 
                                ORDER BY
                                    prices.for_date DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
                            )
                        )
                    )
                )
            )
        )
) AS union1