sql - Using partition by in SQLite

Question

I'm trying to recreate the code used in this article https://blog.getdbt.com/modeling-marketing-attribution/ but the main difference is that I have to use SQLite. I cannot figure out why I am unable to create fields that will form the foundation for me to calculate the number of attribution points to assign to each session. I'm not sure if I am using the "partition by" function properly.

The data translated from the article to mine is:

• Sessions = Impressions
• Customer_ID = Onovative_ID

Any help on this would be much appreciated!

QUERY:

select
*,
count(*) over (
partition by on_id
) as total_sessions,

row_number() over (
partition by on_id
order by sessions.started_at
) as session_number

from
//step 2
(
select impressions.*, loans.opendt 
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where 
impression_date <= opendt
and impression_date > date(opendt,'-30 day')
--order by on_id
) as two

WORKING QUERY:

select impressions.*, loans.opendt 
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where 
impression_date <= opendt
and impression_date > date(opendt,'-30 day')

OUTPUT of WORKING QUERY:

on_id    impression_date              medium               type       opendt
10001    2020-07-02 00:00             Letter    ImageWorks Refi              2020-07-29 00:00
70001    2020-07-20 00:00             Postcard              ImageWorks SmartTrack               2020-07-29 00:00
03301    2020-08-25 11:57             Email     CIQ         2020-09-21 00:00
02201    2020-05-11 00:00             Postcard              ImageWorks SmartTrack               2020-06-01 00:00
50001    2020-09-16 10:21             Email     CIQ         2020-09-28 00:00
10001    2020-09-16 10:21             Email    product offer e-statements email             2020-09-28 00:00
00601    2020-09-19 09:30             Email     CIQ         2020-09-28 00:00
00901    2020-09-16 10:21             Email     CIQ         2020-10-05 00:00
00501    2020-09-16 10:21             Email    product offer e-statements email             2020-10-05 00:00
00101    2020-09-19 09:30             Email     CIQ         2020-10-05 00:00
00401    2020-10-01 09:42             Email     CIQ         2020-10-05 00:00
00801    2020-10-04 16:14             Email     CIQ         2020-10-05 00:00
00301    2020-07-18 11:11             Email     CIQ         2020-07-28 00:00
00012    2020-06-08 11:43             Direct Mail Large Postcard           CIQ         2020-07-03 00:00

**SYNTAX ERROR: (doesn’t give line number)**

``` [14:41:37] Error while executing SQL query on database 'save_pandas3': near "(": syntax error```

Answer 1

等待大神解答