Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Accession
|   AccessionID | SampleReceivedDate__c   |   CustomerID | CreatedDate             | AccessionStatus__c   | Test Sample Received   | Report_Date__c          | Date of First Report    | Date of Last Report     | Date of First Report 2021   | WeekEnding              |
|--------------:|:------------------------|-------------:|:------------------------|:---------------------|:-----------------------|:------------------------|:------------------------|:------------------------|:----------------------------|:------------------------|
|        316613 | 2021-02-26 00:00:00.000 |     16071325 | 2021-02-26 00:00:00.000 | Completed            | Sample Received        | 2021-03-04 21:50:42.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        314903 | 2021-02-11 00:00:00.000 |     16071325 | 2021-02-11 00:00:00.000 | Completed            | Sample Received        | 2021-02-17 19:57:57.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        311566 | 2021-01-14 00:00:00.000 |     16071325 | 2021-01-14 00:00:00.000 | Completed            | Sample Received        | 2021-01-15 17:38:41.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        311562 | 2021-01-14 00:00:00.000 |     16071325 | 2021-01-14 00:00:00.000 | Completed            | Sample Received        | 2021-01-15 17:38:36.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        311568 | 2021-01-14 00:00:00.000 |     16071325 | 2021-01-14 00:00:00.000 | Completed            | Sample Received        | 2021-01-15 17:38:44.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        311563 | 2021-01-14 00:00:00.000 |     16071325 | 2021-01-14 00:00:00.000 | Completed            | Sample Received        | 2021-01-15 17:38:38.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        310007 | 2020-12-30 00:00:00.000 |     16071325 | 2020-12-30 00:00:00.000 | Completed            | Sample Received        | 2021-01-05 20:35:31.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        310255 | 2021-01-04 00:00:00.000 |     16071325 | 2021-01-04 00:00:00.000 | Completed            | Sample Received        | 2021-01-07 18:16:44.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        311067 | 2021-01-11 00:00:00.000 |     16071325 | 2021-01-11 00:00:00.000 | Completed            | Sample Received        | 2021-01-15 18:05:07.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |
|        312421 | 2021-01-21 00:00:00.000 |     16071325 | 2021-01-21 00:00:00.000 | Completed            | Sample Received        | 2021-01-28 18:15:29.000 | 2021-01-04 19:45:43.000 | 2021-03-10 22:59:39.000 | 2021-01-04 19:45:43.000     | 2021-01-09 00:00:00.000 |


Table: CalendarAccessionCreated
| DateKey                 |   Year |   Month |   Day |   Week of Year | Week Ending             | Month Name   |   Quarter | MonthYear               |
|:------------------------|-------:|--------:|------:|---------------:|:------------------------|:-------------|----------:|:------------------------|
| 2018-07-08 00:00:00.000 |   2018 |       7 |     8 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-09 00:00:00.000 |   2018 |       7 |     9 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-10 00:00:00.000 |   2018 |       7 |    10 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-11 00:00:00.000 |   2018 |       7 |    11 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-12 00:00:00.000 |   2018 |       7 |    12 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-13 00:00:00.000 |   2018 |       7 |    13 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2018-07-14 00:00:00.000 |   2018 |       7 |    14 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 |
| 2019-07-07 00:00:00.000 |   2019 |       7 |     7 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 |
| 2019-07-08 00:00:00.000 |   2019 |       7 |     8 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 |
| 2019-07-09 00:00:00.000 |   2019 |       7 |     9 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 |


Table: CalendarAccessionSampleRecd
| DateKey                 |   Year |   Month |   Day |   Week of Year | Week Ending             | Month Name   |   Quarter | MonthYear               | BeforeToday   |
|:------------------------|-------:|--------:|------:|---------------:|:------------------------|:-------------|----------:|:------------------------|:--------------|
| 2018-07-08 00:00:00.000 |   2018 |       7 |     8 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-09 00:00:00.000 |   2018 |       7 |     9 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-10 00:00:00.000 |   2018 |       7 |    10 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-11 00:00:00.000 |   2018 |       7 |    11 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-12 00:00:00.000 |   2018 |       7 |    12 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-13 00:00:00.000 |   2018 |       7 |    13 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2018-07-14 00:00:00.000 |   2018 |       7 |    14 |             28 | 2018-07-14 00:00:00.000 | July         |         3 | 2018-07-01 00:00:00.000 | True          |
| 2019-07-07 00:00:00.000 |   2019 |       7 |     7 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 | True          |
| 2019-07-08 00:00:00.000 |   2019 |       7 |     8 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 | True          |
| 2019-07-09 00:00:00.000 |   2019 |       7 |     9 |             28 | 2019-07-13 00:00:00.000 | July         |         3 | 2019-07-01 00:00:00.000 | True          |


Table: ClinicianandLead
|   CustomerID | Date of First Report 2021   | Date of Web Registration   |
|-------------:|:----------------------------|:---------------------------|
|   1457463036 |                             |                            |
|   1194913384 |                             |                            |
|   1275506099 |                             |                            |
|   1568529519 |                             |                            |
|   1447463898 |                             |                            |
|   1124370523 |                             |                            |
|   1073771648 |                             |                            |
|   1700177573 |                             |                            |
|   1801964648 |                             |                            |
|   1932493913 |                             |                            |


Table: HCP Reg Calendar
| Date                    |   day |   week |   month |   year |   day index |   week index |   month index |   year index | Month Year              | WeekEnding              |
|:------------------------|------:|-------:|--------:|-------:|------------:|-------------:|--------------:|-------------:|:------------------------|:------------------------|
| 2021-01-01 00:00:00.000 |     1 |      1 |       1 |   2021 |        -590 |          -85 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-02 00:00:00.000 |
| 2021-01-02 00:00:00.000 |     2 |      1 |       1 |   2021 |        -589 |          -85 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-02 00:00:00.000 |
| 2021-01-03 00:00:00.000 |     3 |      2 |       1 |   2021 |        -588 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-04 00:00:00.000 |     4 |      2 |       1 |   2021 |        -587 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-05 00:00:00.000 |     5 |      2 |       1 |   2021 |        -586 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-06 00:00:00.000 |     6 |      2 |       1 |   2021 |        -585 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-07 00:00:00.000 |     7 |      2 |       1 |   2021 |        -584 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-08 00:00:00.000 |     8 |      2 |       1 |   2021 |        -583 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-09 00:00:00.000 |     9 |      2 |       1 |   2021 |        -582 |          -84 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-09 00:00:00.000 |
| 2021-01-10 00:00:00.000 |    10 |      3 |       1 |   2021 |        -581 |          -83 |           -19 |           -1 | 2021-01-01 00:00:00.000 | 2021-01-16 00:00:00.000 |


Table: Refresh Date
| Last refresh            |
|:------------------------|
| 2021-03-11 00:00:00.000 |


Table: Web Order Request
| DateSubmittedRegistration   |   CustomerID | RequestorType   | IsConverted   | ValidHCP_AllConversion   | DateLastAccession   | HCP Conversion Date as Web Reg Date   | IsNotConverted   |   HCP Time from Registration to Test | HCP Registration Submission Date   | # TTL Valid Tests (New HCP's)   | Date of First Accession After Registration   | DateFirstAccession   |
|:----------------------------|-------------:|:----------------|:--------------|:-------------------------|:--------------------|:--------------------------------------|:-----------------|-------------------------------------:|:-----------------------------------|:--------------------------------|:---------------------------------------------|:---------------------|
| 2021-01-01 00:00:00.000     |   1841424603 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44197 | 2021-01-01 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-02 00:00:00.000     |   1528337953 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44198 | 2021-01-02 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-04 00:00:00.000     |   1750815528 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44200 | 2021-01-04 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-05 00:00:00.000     |   1669928347 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44201 | 2021-01-05 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-07 00:00:00.000     |   1053937599 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44203 | 2021-01-07 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-07 00:00:00.000     |   1043395486 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44203 | 2021-01-07 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-07 00:00:00.000     |   1730425281 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44203 | 2021-01-07 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-07 00:00:00.000     |   1477969228 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44203 | 2021-01-07 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-09 00:00:00.000     |   1962060848 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44205 | 2021-01-09 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |
| 2021-01-09 00:00:00.000     |   1386891372 | New             | #ERROR        | Not a Conversion         |                     |                                       | True             |                                44205 | 2021-01-09 00:00:00.000            |                                 | 2018-12-04 00:00:00.000                      |                      |


Table: WebFormSubmission
|   CustomerID | Conversion Date         |
|-------------:|:------------------------|
|   1922098581 | 2021-03-10 00:00:00.000 |
|   1477141380 | 2021-03-10 00:00:00.000 |
|   1023656048 | 2021-03-10 00:00:00.000 |
|   1326128737 | 2021-03-10 00:00:00.000 |
|   1225457666 | 2021-03-10 00:00:00.000 |
|   1699700674 | 2021-03-10 00:00:00.000 |
|   1104002435 | 2021-03-10 00:00:00.000 |
|   1679534598 | 2021-03-10 00:00:00.000 |
|   1598778409 | 2021-03-10 00:00:00.000 |
|   1174591374 | 2021-03-09 00:00:00.000 |

 


tables: name: Accession
data: Accession.csv
name: CalendarAccessionCreated
data: CalendarAccessionCreated.csv
name: CalendarAccessionSampleRecd
data: CalendarAccessionSampleRecd.csv
name: ClinicianandLead
data: ClinicianandLead.csv
name: HCP Reg Calendar
data: HCP Reg Calendar.csv
name: Refresh Date
data: Refresh Date.csv
name: Web Order Request
data: Web Order Request.csv
name: WebFormSubmission
data: WebFormSubmission.csv
label: Accession
CustomerID
Web Order Request
CustomerID
Accession
SampleReceivedDate__c
CalendarAccessionSampleRecd
DateKey
Accession
CreatedDate
CalendarAccessionCreated
DateKey
Accession
CustomerID
ClinicianandLead
CustomerID
output: {
    "joins": [
        {
            "from_table": "Accession",
            "from_column": "CustomerID",
            "to_table": "Web Order Request",
            "to_column": "CustomerID"
        },
        {
            "from_table": "Accession",
            "from_column": "SampleReceivedDate__c",
            "to_table": "CalendarAccessionSampleRecd",
            "to_column": "DateKey"
        },
        {
            "from_table": "Accession",
            "from_column": "CreatedDate",
            "to_table": "CalendarAccessionCreated",
            "to_column": "DateKey"
        },
        {
            "from_table": "Accession",
            "from_column": "CustomerID",
            "to_table": "ClinicianandLead",
            "to_column": "CustomerID"
        }
    ]
}