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: Common Date Table
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | MonthName   | MonthInCalendar   | QuarterInCalendar   |   DayInWeek | DayOfWeekName   | WeekEnding              |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:------------|:------------------|:--------------------|------------:|:----------------|:------------------------|
| 2019-07-01 00:00:00.000 |   2019 |               3 |             7 |            1 |  20190701 | July        | Jul 2019          | Q3 2019             |           1 | Monday          | 2019-07-06 00:00:00.000 |
| 2019-07-02 00:00:00.000 |   2019 |               3 |             7 |            2 |  20190702 | July        | Jul 2019          | Q3 2019             |           2 | Tuesday         | 2019-07-06 00:00:00.000 |
| 2019-07-03 00:00:00.000 |   2019 |               3 |             7 |            3 |  20190703 | July        | Jul 2019          | Q3 2019             |           3 | Wednesday       | 2019-07-06 00:00:00.000 |
| 2019-07-04 00:00:00.000 |   2019 |               3 |             7 |            4 |  20190704 | July        | Jul 2019          | Q3 2019             |           4 | Thursday        | 2019-07-06 00:00:00.000 |
| 2019-07-05 00:00:00.000 |   2019 |               3 |             7 |            5 |  20190705 | July        | Jul 2019          | Q3 2019             |           5 | Friday          | 2019-07-06 00:00:00.000 |
| 2019-07-06 00:00:00.000 |   2019 |               3 |             7 |            6 |  20190706 | July        | Jul 2019          | Q3 2019             |           6 | Saturday        | 2019-07-06 00:00:00.000 |
| 2019-07-07 00:00:00.000 |   2019 |               3 |             7 |            7 |  20190707 | July        | Jul 2019          | Q3 2019             |           0 | Sunday          | 2019-07-13 00:00:00.000 |
| 2019-07-08 00:00:00.000 |   2019 |               3 |             7 |            8 |  20190708 | July        | Jul 2019          | Q3 2019             |           1 | Monday          | 2019-07-13 00:00:00.000 |
| 2019-07-09 00:00:00.000 |   2019 |               3 |             7 |            9 |  20190709 | July        | Jul 2019          | Q3 2019             |           2 | Tuesday         | 2019-07-13 00:00:00.000 |
| 2019-07-10 00:00:00.000 |   2019 |               3 |             7 |           10 |  20190710 | July        | Jul 2019          | Q3 2019             |           3 | Wednesday       | 2019-07-13 00:00:00.000 |


Table: Competitive Intelligence
| Item Name                                                                                                                                                                                         |   Rating |   Price |   Competitor Rating |   Competitor Price |   Units Sold |
|:--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------:|--------:|--------------------:|-------------------:|-------------:|
| Lenovo ThinkPad T490s Business Laptop, 14.0" FHD (1920 x 1080) IPS Touchscreen Notebook, Intel i7-8665U Processor, 8GB DDR4 RAM, 512GB PCIe SSD, Windows 10 Pro 64-bit                            |      5   |    1499 |                 4.5 |               1599 |        19524 |
| Dell Latitude 5300 13.3-inch FHD 512GB SSD Touchscreen 2-in-1 Laptop (16GB RAM, Quad-Core i7-8665U up to 4.8GHz, Integrated UHD 620 Graphics, Windows 10 Pro) Black                               |      4   |     799 |                 4.2 |                750 |        35693 |
| HP ENVY 13 Inch Thin Laptop w/ Fingerprint Reader, 4K Touchscreen, Intel Core i7-8565U, NVIDIA GeForce MX250 Graphics, 16GB SDRAM, 512GB SSD, Windows 10 Home (13-aq0044nr, Natural Silver)       |      4   |     899 |                 4.3 |                999 |        15142 |
| Razer Blade 15 Gaming Laptop 2019: Intel Core i7-9750H 6 Core, NVIDIA GeForce RTX 2060, 15.6" FHD 1080p 144Hz, 16GB RAM, 512GB SSD, CNC Aluminum, Chroma RGB Lighting, Thunderbolt 3, Quartz Pink |      4.6 |     650 |                 4.4 |                699 |        16719 |
| 2020 HP 15.6" Touchscreen Laptop Computer, 10th Gen Intel Quard-Core i5 1035G1(Beats i7-7500U), 32GB DDR4 RAM, 2TB PCIe SSD, AC WiFi, Silver, Windows 10 Home + YZAKKA External DVD Drive         |      4.4 |     599 |                 4.1 |                750 |        17151 |


Table: Geo Data
|   Item Number | Category   | Product Name                                      | State ID   |   Sales ($) |   Weekly Sales ($) Variance to  LY |   Count of Stores |   Store Velocity |
|--------------:|:-----------|:--------------------------------------------------|:-----------|------------:|-----------------------------------:|------------------:|-----------------:|
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | AK         |    10357.3  |                        -0.071878   |                 5 |         2071.46  |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | AL         |    36405    |                         0.0061945  |                39 |          933.461 |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | AR         |    21109.8  |                         0.0256195  |                20 |         1055.49  |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | AZ         |    22412.8  |                         0.108002   |                32 |          700.399 |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | CA         |   100834    |                         0.0348133  |               111 |          908.41  |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | CO         |    28691.2  |                        -0.0373042  |                29 |          989.352 |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | CT         |    13076.8  |                        -0.00191442 |                17 |          769.225 |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | DC         |      725.62 |                        -0.228513   |                 1 |          725.62  |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | DE         |    10167.2  |                        -0.0407346  |                10 |         1016.72  |
|         18439 | Tablets    | Apple iPad (Wi-Fi, 32GB) - White (Previous Model) | FL         |    94479.3  |                         0.0437552  |               123 |          768.124 |


Table: In-Stock Data
|   Item Number | Category   | Product Name                                           | Week ID                 |   Week Number ID |   Average Inventory On Hand ($) |   In-Stock % |   Sales ($) |
|--------------:|:-----------|:-------------------------------------------------------|:------------------------|-----------------:|--------------------------------:|-------------:|------------:|
|         18443 | Tablets    | Apple iPad (Wi-Fi, 16GB) - White (Previous Model)      | 2019-11-29 00:00:00.000 |               43 |                          441.76 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-12-13 00:00:00.000 |               45 |                          513.54 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-11-15 00:00:00.000 |               41 |                           90.01 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-12-20 00:00:00.000 |               46 |                           88.85 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2020-01-24 00:00:00.000 |               51 |                           85.4  |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2020-01-10 00:00:00.000 |               49 |                          112.57 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2020-01-24 00:00:00.000 |               51 |                          106.08 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-07-19 00:00:00.000 |               24 |                          164.6  |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-09-06 00:00:00.000 |               31 |                          154.12 |            1 |           0 |
|         18520 | Tablets    | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model) | 2019-11-01 00:00:00.000 |               39 |                          122.84 |            1 |           0 |


Table: Item Fact Table
|   Item Number | Category           | Product Name                                                                                                                                                                                            | Rating   | Price   | Competitor Rating   | Competitor Price   | Units Sold   |
|--------------:|:-------------------|:--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:---------|:--------|:--------------------|:-------------------|:-------------|
|         12346 | Monitors           | VINTEZ 24 Inch - Gold - High Clarity - Computer Privacy Screen Filter for Widescreen Computer Monitor - Anti-Scratch Protector Film for Data confidentiality - 16:9 Aspect Ratio                        |          |         |                     |                    |              |
|         18432 | Premium Computers  | Razer Blade 15 Gaming Laptop 2019: Intel Core i7-9750H 6 Core, NVIDIA GeForce RTX 2060, 15.6" FHD 1080p 144Hz, 16GB RAM, 512GB SSD, CNC Aluminum, Chroma RGB Lighting, Thunderbolt 3, Quartz Pink       | 4.6      | 650     | 4.4                 | 699                | 16719        |
|         18439 | Tablets            | Apple iPad (Wi-Fi, 32GB) - White (Previous Model)                                                                                                                                                       |          |         |                     |                    |              |
|         18442 | Tablets            | Fire HD 8 Tablet (10" HD Display, 16 GB) - Black                                                                                                                                                        |          |         |                     |                    |              |
|         18443 | Tablets            | Apple iPad (Wi-Fi, 16GB) - White (Previous Model)                                                                                                                                                       |          |         |                     |                    |              |
|         18446 | Low Cost Computers | 2020 HP 14“ Laptop (AMD A9-9425 up to 3.7 GHz, 4GB DDR4 RAM, 128GB SSD, AMD Radeon R5 Graphic, Wi-Fi, Bluetooth, HDMI, Windows 10 Home)                                                                 |          |         |                     |                    |              |
|         18491 | Monitors           | 27 Inch 16:9 Aspect Ratio Computer Privacy Screen Filter for Widescreen Computer Monitor 23.6"x13.3" Anti-Glare - Anti-Scratch Protector Film for Data Confidentiality - We Offer 2 Different 27" Sizes |          |         |                     |                    |              |
|         18520 | Tablets            | Apple iPad (Wi-Fi, 32GB) - Space Gray (Previous Model)                                                                                                                                                  |          |         |                     |                    |              |
|         18581 | Computer Monitors  | 27''W Inch Privacy Screen Filter for Desktop Computer Widescreen Monitor - Anti-Glare, Blocks 96% UV,Anti-Scratch with 16:9 Aspect Ratio                                                                |          |         |                     |                    |              |
|         18595 | Tablets            | Apple iPad (Wi-Fi, 16GB) - Space Gray (Previous Model)                                                                                                                                                  |          |         |                     |                    |              |


Table: Marketing Data
|   CVR | Date                    |   Product Number | Default Channel Grouping   | Device Category   |   Number of Records | Region         |   Revenue |   Sessions | Source   |   Transactions |
|------:|:------------------------|-----------------:|:---------------------------|:------------------|--------------------:|:---------------|----------:|-----------:|:---------|---------------:|
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Maryland       |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | New York       |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | North Carolina |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Pennsylvania   |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Rhode Island   |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | South Carolina |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Tennessee      |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Alabama        |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | Arkansas       |         0 |          1 | google   |              0 |
|     0 | 2019-01-01 00:00:00.000 |                0 | Organic Search             | desktop           |                   1 | California     |         0 |          1 | google   |              0 |


Table: Survey Data
|   Respondent Number | Gender   |   Age |   Product Number | Question                                  |   Answer |
|--------------------:|:---------|------:|-----------------:|:------------------------------------------|---------:|
|                   1 | Male     |    35 |            98028 | What would you rate this product overall? |        3 |
|                   1 | Male     |    35 |            98028 | Usablility(1-5)                           |        4 |
|                   1 | Male     |    35 |            98028 | Hardware Qualitiy(1-5)                    |        2 |
|                   1 | Male     |    35 |            98028 | Software Quality(1-5)                     |        5 |
|                   2 | Female   |    54 |            25621 | What would you rate this product overall? |        3 |
|                   2 | Female   |    54 |            25621 | Usablility(1-5)                           |        2 |
|                   2 | Female   |    54 |            25621 | Hardware Qualitiy(1-5)                    |        1 |
|                   2 | Female   |    54 |            25621 | Software Quality(1-5)                     |        4 |
|                   3 | Male     |    52 |            26626 | What would you rate this product overall? |        5 |
|                   3 | Male     |    52 |            26626 | Usablility(1-5)                           |        5 |

 


tables: name: Common Date Table
data: Common Date Table.csv
name: Competitive Intelligence
data: Competitive Intelligence.csv
name: Geo Data
data: Geo Data.csv
name: In-Stock Data
data: In-Stock Data.csv
name: Item Fact Table
data: Item Fact Table.csv
name: Marketing Data
data: Marketing Data.csv
name: Survey Data
data: Survey Data.csv
label: Geo Data
Product Name
Item Fact Table
Product Name
In-Stock Data
Product Name
Item Fact Table
Product Name
Marketing Data
Product Number
Item Fact Table
Item Number
Survey Data
Gender
Item Fact Table
Item Number
Item Fact Table
Price
Competitive Intelligence
Price
In-Stock Data
Week ID
Common Date Table
Date
Marketing Data
Date
Common Date Table
Date
output: {
    "joins": [
        {
            "from_table": "Geo Data",
            "from_column": "Product Name",
            "to_table": "Item Fact Table",
            "to_column": "Product Name"
        },
        {
            "from_table": "In-Stock Data",
            "from_column": "Product Name",
            "to_table": "Item Fact Table",
            "to_column": "Product Name"
        },
        {
            "from_table": "Marketing Data",
            "from_column": "Product Number",
            "to_table": "Item Fact Table",
            "to_column": "Item Number"
        },
        {
            "from_table": "Survey Data",
            "from_column": "Gender",
            "to_table": "Item Fact Table",
            "to_column": "Item Number"
        },
        {
            "from_table": "Item Fact Table",
            "from_column": "Price",
            "to_table": "Competitive Intelligence",
            "to_column": "Price"
        },
        {
            "from_table": "In-Stock Data",
            "from_column": "Week ID",
            "to_table": "Common Date Table",
            "to_column": "Date"
        },
        {
            "from_table": "Marketing Data",
            "from_column": "Date",
            "to_table": "Common Date Table",
            "to_column": "Date"
        }
    ]
}