Assignment Two by brtivedi5 · Pull Request #3 · brtivedi5/sql

brtivedi5 · 2025-01-06T17:54:51Z

What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)

Changed Delete Q1 and type1 and type 2 explanation added

What did you learn from the changes you have made?

Make sure to commit as required

Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?

No

Were there any challenges? If so, what issue(s) did you face? How did you overcome it?

None

How were these changes tested?

I tested via inbuild test

A reference to a related issue in your repository (if applicable)

NA

Checklist

I can confirm that my changes are working as intended

Summary by Sourcery

Update queries for Assignment 2 and add an explanation of Type 1 and Type 2 slowly changing dimensions.

New Features:

Explain Type 1 and Type 2 slowly changing dimensions.

Enhancements:

Add window functions, string manipulations, unions, cross joins, insert, delete, and update statements to the SQL queries.

Tests:

Test queries using the inbuilt test functionality.

sourcery-ai · 2025-01-06T17:54:55Z

Reviewer's Guide by Sourcery

This pull request completes the second assignment by adding SQL queries for various tasks, including window functions, string manipulations, unions, cross joins, inserts, deletes, and updates.

ER diagram for product_units table creation and modification

erDiagram
    product ||--o{ product_units : "snapshot"
    product {
        int product_id
        string product_name
        string product_size
        int product_category_id
        string product_qty_type
    }
    product_units {
        int product_id
        string product_name
        string product_size
        int product_category_id
        string product_qty_type
        timestamp snapshot_timestamp
        int current_quantity
    }

ER diagram for Type 1 vs Type 2 customer address storage

erDiagram
    %% Type 1 Approach
    CUSTOMER_TYPE1 ||--|| ADDRESS_TYPE1 : has
    CUSTOMER_TYPE1 {
        int customer_id
        string name
        string current_address
    }
    ADDRESS_TYPE1 {
        string address
        timestamp last_updated
    }

    %% Type 2 Approach
    CUSTOMER_TYPE2 ||--o{ ADDRESS_TYPE2 : has
    CUSTOMER_TYPE2 {
        int customer_id
        string name
    }
    ADDRESS_TYPE2 {
        int address_id
        int customer_id
        string address
        date valid_from
        date valid_to
        boolean is_current
    }

File-Level Changes

Change	Details	Files
Added SQL queries for window functions to assign visit numbers and calculate product purchase counts.	Implemented `ROW_NUMBER()` and `DENSE_RANK()` window functions to number customer visits. Used `COUNT()` window function to track the number of times each customer purchased a specific product_id over time. Reversed the numbering of visits to label the most recent visit as 1 and filtered for it using a subquery	`02_activities/assignments/assignment2.sql`
Added SQL queries for string manipulations to extract descriptions and filter product sizes.	Used `INSTR()` and `SUBSTR()` functions to extract product descriptions from the product name. Applied `TRIM()` to remove leading/trailing whitespaces from extracted descriptions. Used `REGEXP` to filter product sizes containing numerical values. Used `COALESCE` to handle null values in string concatenation	`02_activities/assignments/assignment2.sql`
Added SQL queries for set operations (UNION) to combine results from different queries.	Created temporary tables to store intermediate results. Used `UNION` to combine results for the best and worst sales days. Used `RANK()` to determine the best and worst sales days based on total sales	`02_activities/assignments/assignment2.sql`
Added SQL queries for cross joins to calculate potential vendor earnings.	Used `CROSS JOIN` to combine vendor and customer data. Calculated total money made by each vendor for each product by multiplying the number of customers with the original price	`02_activities/assignments/assignment2.sql`
Added SQL queries for data manipulation operations (INSERT, DELETE, UPDATE).	Created a new table `product_units` and inserted data from the `product` table. Added a new column `snapshot_timestamp` with the current timestamp. Inserted a new row into the `product_units` table. Deleted older records from the `product_units` table based on the timestamp. Updated the `product_units` table to add `current_quantity` from the `vendor_inventory` table	`02_activities/assignments/assignment2.sql`
Updated assignment description to include explanations of Type 1 and Type 2 slowly changing dimensions.	Added an explanation of Type 1 (overwrite) and Type 2 (historical tracking) slowly changing dimensions for handling changes in customer addresses	`02_activities/assignments/Assignment2.md`

Tips and commands

Interacting with Sourcery

Trigger a new review: Comment @sourcery-ai review on the pull request.
Continue discussions: Reply directly to Sourcery's review comments.
Generate a GitHub issue from a review comment: Ask Sourcery to create an
issue from a review comment by replying to it.
Generate a pull request title: Write @sourcery-ai anywhere in the pull
request title to generate a title at any time.
Generate a pull request summary: Write @sourcery-ai summary anywhere in
the pull request body to generate a PR summary at any time. You can also use
this command to specify where the summary should be inserted.

Customizing Your Experience

Access your dashboard to:

Enable or disable review features such as the Sourcery-generated pull request
summary, the reviewer's guide, and others.
Change the review language.
Add, remove or edit custom review instructions.
Adjust other review settings.

Getting Help

Contact our support team for questions or feedback.
Visit our documentation for detailed guides and information.
Keep in touch with the Sourcery team by following us on X/Twitter, LinkedIn or GitHub.

sourcery-ai

Hey @brtivedi5 - I've reviewed your changes - here's some feedback:

Overall Comments:

Consider maintaining consistent SQL formatting throughout - use proper indentation and line breaks to make queries more readable.
Add more inline comments to explain the logic in complex queries, particularly in the cross join and window function sections.

Here's what I looked at during the review

🟡 General issues: 2 issues found
🟢 Security: all looks good
🟢 Testing: all looks good
🟢 Complexity: all looks good
🟢 Documentation: all looks good

Sourcery is free for open source - if you like our reviews please consider sharing them ✨

_{Help me be more useful! Please click 👍 or 👎 on each comment and I'll use the feedback to improve your reviews.}

sourcery-ai · 2025-01-06T17:56:02Z

-Your answer...
-```
+
+Type 1: Overwrite the data . ( Only the latest address will be retained per customer)


issue (typo): Typo: Remove period after "data".

There should not be a period after the word "data".

Suggested change

Type 1: Overwrite the data . ( Only the latest address will be retained per customer)

Type 1: Overwrite the data ( Only the latest address will be retained per customer)

sourcery-ai · 2025-01-06T17:56:02Z

+
+Type 1: Overwrite the data . ( Only the latest address will be retained per customer)
+
+Type 2: Retain historical data (All the address will be retained per customer as a new address record is created for each address change)


issue (typo): Typo: "All the address" should be "All addresses".

The correct phrasing is "All addresses".

brtrivedi5 added 2 commits December 19, 2024 00:41

completed assignement 2nd

c98f1d3

changed added as per reauirement

049623f

sourcery-ai Bot reviewed Jan 6, 2025

View reviewed changes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Assignment Two#3

Assignment Two#3
brtivedi5 wants to merge 2 commits intomainfrom
assignment-two

brtivedi5 commented Jan 6, 2025 •

edited by sourcery-ai Bot

Loading

Uh oh!

sourcery-ai Bot commented Jan 6, 2025 •

edited

Loading

Interacting with Sourcery

Customizing Your Experience

Getting Help

Uh oh!

sourcery-ai Bot left a comment

Uh oh!

sourcery-ai Bot Jan 6, 2025

Uh oh!

sourcery-ai Bot Jan 6, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

	Type 1: Overwrite the data . ( Only the latest address will be retained per customer)
	Type 1: Overwrite the data ( Only the latest address will be retained per customer)


		Type 1: Overwrite the data . ( Only the latest address will be retained per customer)

		Type 2: Retain historical data (All the address will be retained per customer as a new address record is created for each address change)

Conversation

brtivedi5 commented Jan 6, 2025 • edited by sourcery-ai Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)

What did you learn from the changes you have made?

Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?

Were there any challenges? If so, what issue(s) did you face? How did you overcome it?

How were these changes tested?

A reference to a related issue in your repository (if applicable)

Checklist

Summary by Sourcery

Uh oh!

sourcery-ai Bot commented Jan 6, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Reviewer's Guide by Sourcery

ER diagram for product_units table creation and modification

ER diagram for Type 1 vs Type 2 customer address storage

File-Level Changes

Interacting with Sourcery

Customizing Your Experience

Getting Help

Uh oh!

sourcery-ai Bot left a comment

Choose a reason for hiding this comment

Uh oh!

sourcery-ai Bot Jan 6, 2025

Choose a reason for hiding this comment

Uh oh!

sourcery-ai Bot Jan 6, 2025

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

brtivedi5 commented Jan 6, 2025 •

edited by sourcery-ai Bot

Loading

sourcery-ai Bot commented Jan 6, 2025 •

edited

Loading