search

COOL-cohort / COOL

the source code of the COOL system
https://www.comp.nus.edu.sg/~dbsystem/cool/
Apache License 2.0
45 stars 16 forks source link

ecommerce usecase #45

Closed raghavchalapathy closed 2 years ago

raghavchalapathy commented 2 years ago

added ecommerce usecase - monthly cohort query

KimballCai commented 2 years ago

Need to translate the SQL language (i.e., query.sql) to our language (i.e., query.json).

KimballCai commented 2 years ago

Hi raghavchalapathy,

I have checked the sample eCommerce dataset, and I have found several problems in this dataset. For example, there are nan data in the dataset and the format of the Report_Date is not supported in the COOL system.

I have cleaned this dataset and sent a pull request to your local repo. Please merge it if there is nothing wrong.

Besides, I also checked your SQL file and found that I cannot run this script because this script contains language mistakes.

Please fix this mistake and we can also make a comparison.

We are translating this script into our COOL system now.

Best regards, Qingpeng

raghavchalapathy commented 2 years ago

Hi Please find my comments

Comment 1 : I have cleaned this dataset and sent a pull request to your local repo. Please merge it if there is nothing wrong. Raghav: If your tool accepts this mm/dd/YYYY I have no problem Hence I have accepted the merge to my repo thanks. I was expecting the cool to give options to handle "nan" interesting that we need to fix them before, thanks for this info and many thanks for cleaning it,

Comment 2: Besides, I also checked your SQL file and found that I cannot run this script because this script contains language mistakes. Please fix this mistake and we can also make a comparison.

Raghav: I have run the SQL in BIgQuery SQL interface and Language, Do you want me to port the code in PostgresSQL format? Is that acceptable for you ? Kindly confirm thanks

KimballCai commented 2 years ago

Hi Please find my comments

Comment 1 : I have cleaned this dataset and sent a pull request to your local repo. Please merge it if there is nothing wrong. Raghav: If your tool accepts this mm/dd/YYYY I have no problem Hence I have accepted the merge to my repo thanks. I was expecting the cool to give options to handle "nan" interesting that we need to fix them before, thanks for this info and many thanks for cleaning it,

This is a problem, and we will arrange to add this function later.

Comment 2: Besides, I also checked your SQL file and found that I cannot run this script because this script contains language mistakes. Please fix this mistake and we can also make a comparison.

Raghav: I have run the SQL in BIgQuery SQL interface and Language, Do you want me to port the code in PostgresSQL format? Is that acceptable for you ? Kindly confirm thanks

Can you provide the results that were processed by the BIgQuery SQL interface? We need to make sure that we understand your script correctly. Thanks.

raghavchalapathy commented 2 years ago

Hi Team, I have uploaded the sql scripts , results and execution statistics inside the ecommerce folder along with updated datasets used for analysis inside sqlquery- you can find sql scripts and sqlquery-results you can find output (*.csv) for 3 SQL queries 1) Product Retention 2) Product Retention Clustering 3) Cohort Analysis And a Microsoft PPT consisting of execution stats for the respective query

Thanks with regards Raghav

NLGithubWP commented 2 years ago

Thank you very much, We will test this PR those days, and merge soon.

raghavchalapathy commented 2 years ago

great thanks!

On Sun, May 29, 2022 at 9:32 AM working @.***> wrote:

Thank you very much, We will test this PR those days, and merge soon.

— Reply to this email directly, view it on GitHub https://github.com/COOL-cohort/COOL/pull/45#issuecomment-1140483045, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABQESTDOYN7EGSIZ746272TVMOL3RANCNFSM5VWXBDYQ . You are receiving this because you authored the thread.Message ID: @.***>

KimballCai commented 2 years ago

Merge datasets and use-cases into the dev branch, and open another PR(https://github.com/COOL-cohort/COOL/pull/54) to translate cohort queries from the .sql format into our JSON format.

KimballCai commented 2 years ago

After the discussion, some functionalities proposed in the .sql file are not fully supported by the current system, and we need to add some more functions to fulfill these requirements.

KimballCai commented 2 years ago

But we can merge the dataset into our dev branch first.