Strange behaviour of the SELECT

Question

I have two tables: dim_applications with list of applications

/*dim_applications*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[dim_applications](
        [id] [bigint] NOT NULL,
        [country_id] [int] NOT NULL,
        [customer_id] [bigint] NOT NULL,
        [first_status_day_date] [date] NULL,
        [first_status_time_of_day] [time](0) NULL,
        [campaign_id] [int] NULL,
        [campaign_response_time][int] NULL
    ) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND name = N'PK_dim_applications')
ALTER TABLE [dbo].[dim_applications] ADD CONSTRAINT [PK_dim_applications] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND name = N'IX_dim_applications')
CREATE NONCLUSTERED INDEX [IX_dim_applications] ON [dbo].[dim_applications]
(
    [country_id] ASC
)
INCLUDE ([customer_id], [first_status_day_date], [first_status_time_of_day]) 
GO

and log table with CRM messages sent to customers:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'fact_customers_campaigns' AND TABLE_CATALOG = 'dwh')
BEGIN
    CREATE TABLE [dbo].[fact_customers_campaigns](
        [campaign_id] [int] NOT NULL,
        [customer_id] [bigint] NULL,
        [day_date] [date] NOT NULL,
        [time_of_day] [time](0) NOT NULL
        ) ON [PRIMARY]
END 
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fact_customers_campaigns]') AND name = N'IX_fact_customers_campaigns_3')
CREATE NONCLUSTERED INDEX [IX_fact_customers_campaigns_3] ON [dbo].[fact_customers_campaigns]
(
    [customer_id] ASC,
    [day_date] DESC,
    [time_of_day] DESC
)
INCLUDE([campaign_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

What I want to select is "campaign_id" of the closest campaign for every application for a specific country. This campaign must be not "older" than 14 days. Connection of two tables is done by "customer_id".

my select query is like this

SELECT 
    a.id
    ,(SELECT TOP 1 
            c.campaign_id 
        FROM [dwh].[dbo].[fact_customers_campaigns] c 
        WHERE 1 = 1
            AND c.[customer_id] = a.[customer_id] 
            AND DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14
            ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)
FROM [dwh].[dbo].[dim_applications] a
WHERE 1 = 1
    AND a.[country_id] = 1

it works fine, returns 1.5 million records in 7 seconds. But, when I run the same query with country_id of the last country in my list (14), the very same query becomes very slow and may take up to 2 minutes (1.2 million records). Why is there such diffrence? Plans are the same. I just don't understand this behaviour. Server versions are

Microsoft SQL Server 2012 (SP1) - 11.0.3339.0 (X64) 
    Jan 14 2013 19:02:10 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

and

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 
    Dec  9 2011 11:27:20 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

How can I make execution time to be independent of the selected county_id? Here is the plan for country_id = 14, SQL Server 2012 SP1

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.3339.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1253920" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="258.447" StatementText="SELECT &#xD;&#xA;    a.id&#xD;&#xA;    ,(SELECT TOP 1 &#xD;&#xA;            c.campaign_id &#xD;&#xA;        FROM [dwh].[dbo].[fact_customers_campaigns] c &#xD;&#xA;        WHERE 1 = 1&#xD;&#xA;            AND c.[customer_id] = a.[customer_id] &#xD;&#xA;            AND DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14&#xD;&#xA;            ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)&#xD;&#xA;FROM [dwh].[dbo].[dim_applications] a&#xD;&#xA;WHERE 1 = 1&#xD;&#xA;    AND a.[country_id] = 14" StatementType="SELECT" QueryHash="0x9FA79BEF6AA009EB" QueryPlanHash="0x98FC15D00C35255D" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="8" MemoryGrant="136" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="488">
            <ThreadStat Branches="1" UsedThreads="8">
              <ThreadReservation NodeId="0" ReservedThreads="8" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" RequiredMemory="136" DesiredMemory="136" RequestedMemory="136" GrantWaitTime="0" GrantedMemory="136" MaxUsedMemory="136" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="314572" EstimatedPagesCached="157286" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="19" EstimateCPU="0.125392" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1253920" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="258.447">
              <OutputList>
                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                <ColumnReference Column="Expr1006" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[dwh].[dbo].[fact_customers_campaigns].[campaign_id] as [c].[campaign_id]">
                      <Identifier>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </Identifier>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="19" EstimateCPU="0.940258" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1253920" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="258.322">
                  <OutputList>
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1253922" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <RelOp AvgRowSize="19" EstimateCPU="1.31035" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1253920" LogicalOp="Left Outer Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="257.382">
                      <OutputList>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="4" ActualRows="86240" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="8" ActualRows="219041" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="5" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="3" ActualRows="86241" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="7" ActualRows="258720" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="6" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="1" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="2" ActualRows="86240" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          <ColumnReference Column="Expr1008" />
                        </OuterReferences>
                        <RelOp AvgRowSize="26" EstimateCPU="0.344868" EstimateIO="4.14941" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1253920" LogicalOp="Index Seek" NodeId="4" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.49428" TableCardinality="6016420">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="8" ActualRows="219041" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="4" ActualRows="86240" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="5" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="3" ActualRows="86241" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="7" ActualRows="258720" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="6" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="172480" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="86240" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Index="[IX_dim_applications]" Alias="[a]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="country_id" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="(14)">
                                        <Const ConstValue="(14)" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="1253920" EstimateRewinds="0.134582" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="true" PhysicalOp="Top" EstimatedTotalSubtreeCost="251.577">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="86240" ActualExecutions="86240" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="8" ActualRows="36405" Batches="0" ActualEndOfScans="182636" ActualExecutions="219041" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="5" ActualRows="11849" Batches="0" ActualEndOfScans="160631" ActualExecutions="172480" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="3" ActualRows="1367" Batches="0" ActualEndOfScans="84874" ActualExecutions="86241" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="7" ActualRows="47272" Batches="0" ActualEndOfScans="211448" ActualExecutions="258720" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="6" ActualRows="22849" Batches="0" ActualEndOfScans="149631" ActualExecutions="172480" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="19968" Batches="0" ActualEndOfScans="152512" ActualExecutions="172480" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="86240" ActualExecutions="86240" ActualExecutionMode="Row" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <Top RowCount="false" IsPercent="false" WithTies="false">
                            <TopExpression>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </TopExpression>
                            <RelOp AvgRowSize="25" EstimateCPU="0.000167189" EstimateIO="0.003125" EstimateRebinds="1253920" EstimateRewinds="0.134582" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="7" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="229.491" TableCardinality="1606160">
                              <OutputList>
                                <ColumnReference Column="Bmk1002" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="4" ActualRows="0" Batches="0" ActualEndOfScans="86240" ActualExecutions="86240" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="8" ActualRows="36405" Batches="0" ActualEndOfScans="182636" ActualExecutions="219041" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="5" ActualRows="11849" Batches="0" ActualEndOfScans="160631" ActualExecutions="172480" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="3" ActualRows="1367" Batches="0" ActualEndOfScans="84874" ActualExecutions="86241" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="7" ActualRows="47272" Batches="0" ActualEndOfScans="211448" ActualExecutions="258720" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="6" ActualRows="22849" Batches="0" ActualEndOfScans="149631" ActualExecutions="172480" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="19968" Batches="0" ActualEndOfScans="152512" ActualExecutions="172480" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="86240" ActualExecutions="86240" ActualExecutionMode="Row" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Bmk1002" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Index="[IX_fact_customers_campaigns_3]" Alias="[c]" IndexKind="NonClustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="customer_id" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[dwh].[dbo].[dim_applications].[customer_id] as [a].[customer_id]">
                                            <Identifier>
                                              <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                                <Predicate>
                                  <ScalarOperator ScalarString="datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&gt;=(0) AND datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&lt;=(14)">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="GE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(0)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="LE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(14)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </Top>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                  </Parallelism>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answer 1

This query works fine for both cases.

SELECT 
    a.id
    ,(SELECT TOP 1 
            c.campaign_id 
        FROM [dwh].[dbo].[fact_customers_campaigns] c 
        WHERE 1 = 1
            AND c.[customer_id] = a.[customer_id] 
            AND c.[day_date] >= DATEADD(dd, -14, a.[first_status_day_date])
            AND c.[day_date] <= a.[first_status_day_date]
            ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)
FROM [dwh].[dbo].[dim_applications] a
WHERE 1 = 1
    AND a.[country_id] = 14

This version uses indexes properly, and in both cases records are selected quickly.