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: Customer
|   Customer ID | CUSTOMER             | Status   | LINE OF BUSINESS   | CITY     | State   |
|--------------:|:---------------------|:---------|:-------------------|:---------|:--------|
|       1339104 | DWARF ACOUSTICS      | A        | CANTEENS           | WINTHROP | WA      |
|       1339740 | FIRE SOFTWARES       | A        | CANTEENS           | WINTHROP | WA      |
|       1339884 | GRANITE INTELLIGENCE | A        | CANTEENS           | WINTHROP | WA      |
|       1340322 | THORECORDS           | A        | CANTEENS           | WINTHROP | WA      |
|       4837795 | GREEN-PLUS           | A        | CANTEENS           | WINTHROP | WA      |
|       4837804 | DESERPRISES          | A        | CANTEENS           | WINTHROP | WA      |
|       4838120 | TUNDRACOUSTICS       | A        | CANTEENS           | WINTHROP | WA      |
|       4838274 | DINONITE             | A        | CANTEENS           | WINTHROP | WA      |
|       4838418 | CAVEFRUIT            | A        | CANTEENS           | WINTHROP | WA      |
|       4839213 | FLOWERLAND           | A        | CANTEENS           | WINTHROP | WA      |


Table: Dates
| Dates                   |   YEAR | Month Name   |   Month |   Day |
|:------------------------|-------:|:-------------|--------:|------:|
| 2017-01-01 00:00:00.000 |   2017 | January      |       1 |     1 |
| 2017-01-02 00:00:00.000 |   2017 | January      |       1 |     2 |
| 2017-01-03 00:00:00.000 |   2017 | January      |       1 |     3 |
| 2017-01-04 00:00:00.000 |   2017 | January      |       1 |     4 |
| 2017-01-05 00:00:00.000 |   2017 | January      |       1 |     5 |
| 2017-01-06 00:00:00.000 |   2017 | January      |       1 |     6 |
| 2017-01-07 00:00:00.000 |   2017 | January      |       1 |     7 |
| 2017-01-08 00:00:00.000 |   2017 | January      |       1 |     8 |
| 2017-01-09 00:00:00.000 |   2017 | January      |       1 |     9 |
| 2017-01-10 00:00:00.000 |   2017 | January      |       1 |    10 |


Table: Product
|   Product ID | Product    |   Group ID |
|-------------:|:-----------|-----------:|
|            1 | Product 1  |       9999 |
|            2 | Product 2  |       9999 |
|            3 | Product 3  |       9999 |
|            4 | Product 4  |       9999 |
|            5 | Product 5  |       9999 |
|            6 | Product 6  |       9999 |
|            7 | Product 7  |       9999 |
|            8 | Product 8  |       9999 |
|            9 | Product 9  |       9999 |
|           10 | Product 10 |       9999 |


Table: ProductGroup
|   Group ID | PRODUCTS      | CATEGORY   |
|-----------:|:--------------|:-----------|
|        102 | Powdered Milk | Food       |
|        103 | Sugars        | Food       |
|        104 | Canned Sweets | Food       |
|        105 | Flours        | Food       |
|        106 | Oils          | Food       |
|        107 | Olive Oils    | Food       |
|        108 | Coffee Pods   | Food       |
|        109 | Horticulture  | Food       |
|        110 | Wheat Flours  | Food       |
|        111 | Yeast         | Food       |


Table: Sales 2017
| Issue Date              | Due Date                |   Invoice Number |   Customer ID |   SalesPerson ID |   Product ID |   Qty Itens |   Unit Price |   Net Weight |   2017 Sales |
|:------------------------|:------------------------|-----------------:|--------------:|-----------------:|-------------:|------------:|-------------:|-------------:|-------------:|
| 2017-04-02 00:00:00.000 | 2017-04-30 00:00:00.000 |          1561796 |       1211915 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-04-10 00:00:00.000 | 2017-05-04 00:00:00.000 |          1575367 |        673368 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-04-13 00:00:00.000 | 2017-05-21 00:00:00.000 |          1543645 |        673368 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-04-14 00:00:00.000 | 2017-05-16 00:00:00.000 |          1548082 |        624722 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-04-28 00:00:00.000 | 2017-05-15 00:00:00.000 |          1539870 |       3821842 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-04-29 00:00:00.000 | 2017-05-29 00:00:00.000 |          1565380 |        624722 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-05-20 00:00:00.000 | 2017-06-17 00:00:00.000 |          1556410 |       3194155 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-05-26 00:00:00.000 | 2017-07-08 00:00:00.000 |          1605051 |       3194155 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-05-26 00:00:00.000 | 2017-06-15 00:00:00.000 |          1548136 |       3194155 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |
| 2017-05-26 00:00:00.000 | 2017-07-08 00:00:00.000 |          1593640 |       2485067 |              265 |         1154 |           6 |         3.11 |          0.3 |        18.66 |


Table: Sales 2018
| Issue Date              | Due Date                |   Invoice Number |   Customer ID |   SalesPerson ID |   Product ID |   Qty Itens |   Unit Price |   Net Weight |   2018 Sales |
|:------------------------|:------------------------|-----------------:|--------------:|-----------------:|-------------:|------------:|-------------:|-------------:|-------------:|
| 2018-02-09 00:00:00.000 | 2018-03-07 00:00:00.000 |          1877015 |        624722 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-04-11 00:00:00.000 | 2018-05-26 00:00:00.000 |          1945252 |        624722 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-05-18 00:00:00.000 | 2018-06-15 00:00:00.000 |          1969183 |        624722 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-05-27 00:00:00.000 | 2018-06-23 00:00:00.000 |          1986318 |       2842422 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-01 00:00:00.000 | 2018-07-01 00:00:00.000 |          1969509 |       5385827 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-13 00:00:00.000 | 2018-07-14 00:00:00.000 |          2002740 |       5385827 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-15 00:00:00.000 | 2018-07-03 00:00:00.000 |          1959525 |        624722 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-20 00:00:00.000 | 2018-07-22 00:00:00.000 |          1942425 |       1211915 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-21 00:00:00.000 | 2018-07-22 00:00:00.000 |          1949226 |       5385827 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |
| 2018-06-26 00:00:00.000 | 2018-07-18 00:00:00.000 |          1983032 |        673368 |              265 |         1154 |           6 |         3.24 |          0.3 |        19.44 |


Table: Sales 2019
| Issue Date   | Due Date   | Invoice Number   | Customer ID   | SalesPerson ID   | Product ID   | Qty Itens   | Unit Price   | Net Weight   | 2019 Sales   |
|:-------------|:-----------|:-----------------|:--------------|:-----------------|:-------------|:------------|:-------------|:-------------|:-------------|
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |
|              |            |                  |               |                  |              |             |              |              |              |


Table: SalesPerson
|   SalesPerson ID | SalesPerson      |   Supervisor ID | Supervisor             |   Manager ID | Manager     | TEAM         |
|-----------------:|:-----------------|----------------:|:-----------------------|-------------:|:------------|:-------------|
|              669 | Ayaana Mackenzie |              15 | Jonathon Childs        |            1 | Ronnie Daly | Retail       |
|              660 | Tess Sanderson   |              25 | Jessica Valentine      |            1 | Ronnie Daly | Retail       |
|              183 | Hywel Hopkins    |              25 | Jessica Valentine      |            1 | Ronnie Daly | Retail       |
|              102 | Serenity Rich    |              25 | Jessica Valentine      |            1 | Ronnie Daly | Retail       |
|              196 | Gillian Gonzales |              30 | Sarah-Jayne Pennington |            1 | Ronnie Daly | Retail       |
|              215 | Sahil Seymour    |              30 | Sarah-Jayne Pennington |            1 | Ronnie Daly | Retail       |
|              125 | Akeel Mcleod     |              35 | Tahmina Vinson         |            2 | Duane Frame | Distributors |
|              265 | Shakil Durham    |              35 | Tahmina Vinson         |            2 | Duane Frame | Distributors |
|              194 | Kien Mcgee       |              35 | Tahmina Vinson         |            2 | Duane Frame | Distributors |
|              144 | Dominykas Bird   |              55 | Harvey-Lee Solomon     |            2 | Duane Frame | Online       |


Table: targ value
|   SalesPerson ID |        2017 TARG |        2018 TARG |
|-----------------:|-----------------:|-----------------:|
|              102 |      1.81263e+06 |      1.30348e+06 |
|              125 |      1.50851e+06 |      1.28608e+06 |
|              144 |      1.13075e+06 | 966446           |
|              194 |      0           | 427924           |
|              196 |      1.26027e+06 | 825712           |
|              215 |      3.09261e+06 |      2.24624e+06 |
|              265 |      2.14129e+06 |      1.7477e+06  |
|              285 | 509305           |      1.18584e+06 |
|              326 |      0           | 881941           |
|              660 |      0           |  47213.4         |


Table: Targ
|   SalesPerson ID |   01-01-2017 |   01-02-2017 |   01-03-2017 |   01-04-2017 |   01-05-2017 |   01-06-2017 |   01-07-2017 |   01-08-2017 |   01-09-2017 |   01-10-2017 |   01-11-2017 |   01-12-2017 |   01-01-2018 |   01-02-2018 |   01-03-2018 |   01-04-2018 |   01-05-2018 |   01-06-2018 |   01-07-2018 |   01-08-2018 |   01-09-2018 |   01-10-2018 |   01-11-2018 |   01-12-2018 |            Total |
|-----------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-------------:|-----------------:|
|              102 |     140768   |     124259   |     169510   |     143465   |      98423.6 |     133402   |     109125   |     221597   |     101604   |      55255.9 |     261911   |     171051   |      82260.8 |      39925   |     315936   |     105569   |      58743.8 |     181830   |     135931   |       105977 |      67049   |     136594   |      39370.1 |      34296.9 |      3.03385e+06 |
|              125 |      21525.6 |      50778.5 |      80286.3 |     117769   |     162043   |     184889   |     175630   |     120097   |     110608   |     146219   |      69770.2 |     182361   |      86536   |     100701   |      63898.4 |     108730   |      44662.6 |     264251   |      80597.5 |       122251 |     114978   |     199905   |      52701.5 |      46867.6 |      2.70806e+06 |
|              144 |      72301.4 |      47360.5 |      54145.4 |      89457.7 |     115437   |      72356.8 |     164689   |     102331   |     112397   |     106251   |      68987.3 |      78735.9 |      46295.1 |      51108.1 |     126463   |      66398.4 |      99162.6 |      68546.3 |      73499.8 |       168450 |     115770   |     101263   |      16718.9 |      32769.8 |      2.0509e+06  |
|              194 |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |       107118 |     148042   |      90060.2 |      39364.5 |      43339.6 | 427924           |
|              196 |      75253.9 |      56094.1 |     156709   |      82723.1 |      47426.3 |      66398.2 |      91840.5 |     136681   |      87425.6 |     143061   |      75032.9 |     192860   |      48769   |      84907.1 |      99185.1 |      43642.5 |      73916.8 |     114445   |      76255.5 |       138085 |      58425.3 |      67537.6 |      10161.9 |      10380.6 |      2.03722e+06 |
|              215 |     286013   |     268740   |     329813   |     159143   |     117572   |     275379   |     253013   |     329669   |      97924   |     146036   |     434640   |     303755   |      90909   |     190187   |     302684   |     148977   |     141751   |     199967   |     130411   |       354253 |      96238.3 |     389476   |     108939   |      92452.3 |      5.24794e+06 |
|              265 |     122200   |     147008   |     127262   |     206980   |     119510   |     192623   |     181088   |     157471   |     142684   |     229701   |     261399   |     195199   |      58168.1 |     111391   |     254887   |      71697.1 |     151968   |     280318   |      72180.8 |       196108 |     207700   |     258699   |      42788.7 |      41797.7 |      3.83083e+06 |
|              285 |          0   |          0   |          0   |          0   |          0   |      39276   |      35473.3 |      73454.1 |      36858.8 |      75760.6 |     114751   |      90021.6 |      43710.1 |      59640.1 |      98667.7 |      62042.4 |      61236.7 |     223984   |     211970   |       113606 |      61834.8 |     114052   |     103480   |      31620.2 |      1.65144e+06 |
|              326 |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |     108977   |     163633   |     125325   |       183714 |      53283   |     110542   |      80017   |      56449.9 | 881941           |
|              660 |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |          0   |            0 |          0   |          0   |      28839   |      18374.3 |  47213.4         |

 


tables: name: Customer
data: Customer.csv
name: Dates
data: Dates.csv
name: Product
data: Product.csv
name: ProductGroup
data: ProductGroup.csv
name: Sales 2017
data: Sales 2017.csv
name: Sales 2018
data: Sales 2018.csv
name: Sales 2019
data: Sales 2019.csv
name: SalesPerson
data: SalesPerson.csv
name: targ value
data: targ value.csv
name: Targ
data: Targ.csv
label: Product
Group ID
ProductGroup
Group ID
Targ
SalesPerson ID
SalesPerson
SalesPerson ID
Sales 2017
Customer ID
Customer
Customer ID
Sales 2017
Product ID
Product
Product ID
Sales 2017
SalesPerson ID
SalesPerson
SalesPerson ID
Sales 2018
Customer ID
Customer
Customer ID
Sales 2018
Product ID
Product
Product ID
Sales 2018
SalesPerson ID
SalesPerson
SalesPerson ID
Sales 2019
Customer ID
Customer
Customer ID
Sales 2019
Product ID
Product
Product ID
Sales 2019
SalesPerson ID
SalesPerson
SalesPerson ID
Sales 2017
Issue Date
Dates
Dates
Sales 2019
Issue Date
Dates
Dates
Sales 2018
Issue Date
Dates
Dates
Targ
SalesPerson ID
targ value
SalesPerson ID
output: {
    "joins": [
        {
            "from_table": "Product",
            "from_column": "Group ID",
            "to_table": "ProductGroup",
            "to_column": "Group ID"
        },
        {
            "from_table": "Targ",
            "from_column": "SalesPerson ID",
            "to_table": "SalesPerson",
            "to_column": "SalesPerson ID"
        },
        {
            "from_table": "Sales 2017",
            "from_column": "Customer ID",
            "to_table": "Customer",
            "to_column": "Customer ID"
        },
        {
            "from_table": "Sales 2017",
            "from_column": "Product ID",
            "to_table": "Product",
            "to_column": "Product ID"
        },
        {
            "from_table": "Sales 2017",
            "from_column": "SalesPerson ID",
            "to_table": "SalesPerson",
            "to_column": "SalesPerson ID"
        },
        {
            "from_table": "Sales 2018",
            "from_column": "Customer ID",
            "to_table": "Customer",
            "to_column": "Customer ID"
        },
        {
            "from_table": "Sales 2018",
            "from_column": "Product ID",
            "to_table": "Product",
            "to_column": "Product ID"
        },
        {
            "from_table": "Sales 2018",
            "from_column": "SalesPerson ID",
            "to_table": "SalesPerson",
            "to_column": "SalesPerson ID"
        },
        {
            "from_table": "Sales 2019",
            "from_column": "Customer ID",
            "to_table": "Customer",
            "to_column": "Customer ID"
        },
        {
            "from_table": "Sales 2019",
            "from_column": "Product ID",
            "to_table": "Product",
            "to_column": "Product ID"
        },
        {
            "from_table": "Sales 2019",
            "from_column": "SalesPerson ID",
            "to_table": "SalesPerson",
            "to_column": "SalesPerson ID"
        },
        {
            "from_table": "Sales 2017",
            "from_column": "Issue Date",
            "to_table": "Dates",
            "to_column": "Dates"
        },
        {
            "from_table": "Sales 2019",
            "from_column": "Issue Date",
            "to_table": "Dates",
            "to_column": "Dates"
        },
        {
            "from_table": "Sales 2018",
            "from_column": "Issue Date",
            "to_table": "Dates",
            "to_column": "Dates"
        },
        {
            "from_table": "Targ",
            "from_column": "SalesPerson ID",
            "to_table": "targ value",
            "to_column": "SalesPerson ID"
        }
    ]
}