Cy's Data Lab

VBAY Data Warehouse Initialization and Analytics Pipeline

This SQL script sets up a data warehouse environment in Snowflake for an analytics pipeline. It creates a warehouse (WH_VBAY), databases (analytics and raw), schemas (analytics.vbay and analytics.presentation), and defines file formats for loading data. The script stages data from an external Azure Blob Storage location into Snowflake tables for various VBAY entities (e.g., users, items, bids, and ratings). It performs data transformations, such as converting Unix timestamps to readable timestamps and aggregates data to create summary tables for user ratings and top buyers and sellers. Finally, it validates data loads by querying table row counts in the schema.

The script focuses on building a robust foundation for analytics by integrating raw data, cleaning it, and preparing it for analysis in the analytics.presentation schema.

-- Create Warehouse
CREATE OR REPLACE WAREHOUSE WH_VBAY
  WITH 
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Use the WH_VBAY Warehouse
USE WAREHOUSE WH_VBAY;

-- Create Databases
CREATE DATABASE IF NOT EXISTS analytics;
CREATE DATABASE IF NOT EXISTS raw;

-- Create Schemas
CREATE SCHEMA IF NOT EXISTS analytics.vbay; -- Changed to analytics.vbay
CREATE SCHEMA IF NOT EXISTS analytics.presentation;

-- Define File Formats
CREATE OR REPLACE FILE FORMAT analytics.PUBLIC.PARQUET  -- Changed to analytics schema
    TYPE = parquet
    REPLACE_INVALID_CHARACTERS = TRUE;

CREATE OR REPLACE FILE FORMAT analytics.PUBLIC.JSONARRAY
    TYPE = json
    STRIP_OUTER_ARRAY = TRUE;

CREATE OR REPLACE FILE FORMAT analytics.PUBLIC.JSON
    TYPE = json
    STRIP_OUTER_ARRAY = FALSE;

CREATE OR REPLACE FILE FORMAT analytics.PUBLIC.CSVHEADER
    TYPE = 'csv'
    FIELD_DELIMITER  = ','
    SKIP_HEADER = 1;

CREATE OR REPLACE FILE FORMAT analytics.PUBLIC.CSV
    TYPE = csv
    FIELD_DELIMITER  = ','
    PARSE_HEADER = FALSE
    SKIP_HEADER = 0;

-- Create Stage
CREATE OR REPLACE STAGE analytics.PUBLIC.externalworld_database
  URL = 'azure://externalworld.blob.core.windows.net/database/'
  FILE_FORMAT = analytics.PUBLIC.PARQUET;

-- Stage VBAY Users
CREATE OR REPLACE TABLE analytics.vbay.vb_users (  -- Changed to analytics.vbay schema
    user_id VARCHAR,
    user_firstname VARCHAR,
    user_lastname VARCHAR,
    user_email VARCHAR,
    user_zip_code VARCHAR
);
COPY INTO analytics.vbay.vb_users  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_users.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage VBAY Items
-- Create the raw staging table for vb_items
CREATE OR REPLACE TABLE analytics.vbay.vb_items_raw (  
    item_name VARCHAR,
    item_type VARCHAR,
    item_reserve INT,
    item_enddate BIGINT,  -- Store the raw Unix timestamp (microseconds)
    item_sold VARCHAR,
    item_seller_user_id VARCHAR,
    item_buyer_user_id VARCHAR,
    item_soldamount INT
);
-- Load data into the raw staging table vb_items_raw
COPY INTO analytics.vbay.vb_items_raw  -- Load into staging table
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_items.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';
-- Create the final vb_items table with item_enddate as TIMESTAMP
CREATE OR REPLACE TABLE analytics.vbay.vb_items AS
SELECT
    item_name,
    item_type,
    item_reserve,
    TO_TIMESTAMP_LTZ(item_enddate / 1000000) AS item_enddate,  -- Convert item_enddate from microseconds to TIMESTAMP
    item_sold,
    item_seller_user_id,
    item_buyer_user_id,
    item_soldamount
FROM analytics.vbay.vb_items_raw;



-- Stage Item Types Lookup
CREATE OR REPLACE TABLE analytics.vbay.vb_item_types_lookup (  -- Changed to analytics.vbay schema
    item_type VARCHAR
);
COPY INTO analytics.vbay.vb_item_types_lookup  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_item_types_lookup.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage Bid Status Lookup
CREATE OR REPLACE TABLE analytics.vbay.vb_bid_status_lookup (  -- Changed to analytics.vbay schema
    bid_status VARCHAR
);
COPY INTO analytics.vbay.vb_bid_status_lookup  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_bid_status_lookup.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage RateAs Lookup
CREATE OR REPLACE TABLE analytics.vbay.vb_rating_astypes_lookup (  -- Changed to analytics.vbay schema
    rate_astype_lookup VARCHAR
);
COPY INTO analytics.vbay.vb_rating_astypes_lookup  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_rating_astypes_lookup.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage VBAY Zip Codes
CREATE OR REPLACE TABLE analytics.vbay.vb_zip_codes (  -- Changed to analytics.vbay schema
    zip_code VARCHAR,
    zip_city VARCHAR,
    zip_state VARCHAR,
    zip_lat FLOAT,
    zip_lng FLOAT
);
COPY INTO analytics.vbay.vb_zip_codes  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_zip_codes.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage VBAY User Ratings
CREATE OR REPLACE TABLE analytics.vbay.vb_user_ratings (  -- Changed to analytics.vbay schema
    rating_id VARCHAR,
    rating_by_user_id VARCHAR,
    rating_for_user_id VARCHAR,
    rating_astype VARCHAR,
    rating_value INT,
    rating_comment VARCHAR
);
COPY INTO analytics.vbay.vb_user_ratings  -- Changed to analytics.vbay schema
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_user_ratings.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';

-- Stage VBAY Bids
CREATE OR REPLACE TABLE analytics.vbay.vb_bids_raw (  
    bid_id VARCHAR,
    bid_user_id VARCHAR,
    bid_item_id VARCHAR,
    bid_datetime BIGINT,  -- Store the raw Unix timestamp (microseconds)
    bid_amount INT,
    bid_status VARCHAR
);

COPY INTO analytics.vbay.vb_bids_raw  -- Load into staging table
    FROM '@analytics.PUBLIC.externalworld_database/vbay.vb_bids.parquet'
    FILE_FORMAT = analytics.PUBLIC.PARQUET
    MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';
CREATE OR REPLACE TABLE analytics.vbay.vb_bids AS
SELECT
    bid_id,
    bid_user_id,
    bid_item_id,
    TO_TIMESTAMP_LTZ(bid_datetime / 1000000) AS bid_datetime,  -- Convert microseconds to timestamp
    bid_amount,
    bid_status
FROM analytics.vbay.vb_bids_raw;



-- Verify Table Row Counts
SELECT 
    TABLE_CATALOG, 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    ROW_COUNT
FROM 
    analytics.INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = 'VBAY'
ORDER BY 
    TABLE_NAME;

-- User Ratings Summary Table
CREATE OR REPLACE TABLE analytics.presentation.user_ratings_summary AS
SELECT
    rating_for_user_id,
    COUNT(rating_id) AS total_ratings,
    AVG(rating_value) AS avg_rating,
    MAX(rating_value) AS max_rating,
    MIN(rating_value) AS min_rating
FROM analytics.vbay.vb_user_ratings
GROUP BY rating_for_user_id;

-- Top Buyers and Sellers Table
CREATE OR REPLACE TABLE analytics.presentation.top_users AS
SELECT
    item_seller_user_id AS user_id,
    'Seller' AS role,
    COUNT(item_name) AS items_sold,
    SUM(item_soldamount) AS total_revenue
FROM analytics.vbay.vb_items
WHERE item_sold = 'Y'
GROUP BY item_seller_user_id

UNION ALL

SELECT
    item_buyer_user_id AS user_id,
    'Buyer' AS role,
    COUNT(item_name) AS items_bought,
    SUM(item_soldamount) AS total_spent
FROM analytics.vbay.vb_items
WHERE item_sold = 'Y'
GROUP BY item_buyer_user_id;

---

VBAY Data Models in dbt

This script consolidates multiple SQL models built in dbt into one streamlined code block for easier presentation on my website. It defines table materializations for VBAY data sources, such as user information, bids, ratings, and item details, directly from the raw layer, simplifying the structure for better readability and showcasing the ETL process.

-- Rate Astype Lookup Table
{{ config(materialized='table') }}

WITH rate_astype_lookup AS (
    SELECT
        rate_astype_lookup
    FROM {{ source('raw', 'vb_rating_astypes_lookup') }}
)

SELECT
    rate_astype_lookup
FROM rate_astype_lookup;

-- Bid Status Lookup Table
{{ config(materialized='table') }}

WITH bid_status_lookup AS (
    SELECT
        bid_status
    FROM {{ source('raw', 'vb_bid_status_lookup') }}
)

SELECT
    bid_status
FROM bid_status_lookup;

-- Item Types Lookup Table
{{ config(materialized='table') }}

WITH item_types_lookup AS (
    SELECT
        item_type
    FROM {{ source('raw', 'vb_item_types_lookup') }}
)

SELECT
    item_type
FROM item_types_lookup;

-- Users Table
{{ config(materialized='table') }}

WITH users AS (
    SELECT
        user_id,
        user_firstname,
        user_lastname,
        user_email,
        user_zip_code
    FROM {{ source('raw', 'vb_users') }}
)

SELECT
    user_id,
    user_firstname,
    user_lastname,
    user_email,
    user_zip_code
FROM users;

-- Zip Codes Table
{{ config(materialized='table') }}

WITH zip_codes AS (
    SELECT
        zip_code,
        zip_city,
        zip_state,
        zip_lat,
        zip_lng
    FROM {{ source('raw', 'vb_zip_codes') }}
)

SELECT
    zip_code,
    zip_city,
    zip_state,
    zip_lat,
    zip_lng
FROM zip_codes;

WITH bid_data AS (
    SELECT
        b.bid_id,
        b.bid_user_id,
        b.bid_item_id,
        b.bid_datetime,
        b.bid_amount,
        b.bid_status,
        u.user_firstname,
        u.user_lastname
    FROM {{ source('raw', 'vb_bids') }} b
    LEFT JOIN {{ source('raw', 'vb_users') }} u
        ON b.bid_user_id = u.user_id
)

SELECT
    bid_id,
    bid_user_id,
    bid_item_id,
    bid_datetime,
    bid_amount,
    bid_status,
    user_firstname,
    user_lastname
FROM bid_data;

{{ config(materialized='table') }}

WITH items AS (
    SELECT
        item_name,
        item_type,
        item_reserve,
        item_enddate,
        item_sold,
        item_seller_user_id,
        item_buyer_user_id,
        item_soldamount
    FROM {{ source('raw', 'vb_items') }}
)

SELECT
    i.item_name,
    it.item_type,
    i.item_reserve,
    i.item_enddate,
    i.item_sold,
    s.user_firstname AS seller_firstname,
    s.user_lastname AS seller_lastname,
    b.user_firstname AS buyer_firstname,
    b.user_lastname AS buyer_lastname
FROM items i
JOIN {{ ref('dim_vb_item_types_lookup') }} it  -- Correct model for item types
    ON i.item_type = it.item_type
JOIN {{ ref('dim_vb_users') }} s  -- Reference to users model for seller
    ON i.item_seller_user_id = s.user_id
JOIN {{ ref('dim_vb_users') }} b  -- Reference to users model for buyer
    ON i.item_buyer_user_id = b.user_id;

{{ config(materialized='table') }}

WITH user_ratings AS (
    SELECT
        rating_id,
        rating_by_user_id,
        rating_for_user_id,
        rating_astype,
        rating_value,
        rating_comment
    FROM {{ source('raw', 'vb_user_ratings') }}
)

SELECT
    r.rating_id,
    u1.user_firstname AS rated_by_firstname,
    u1.user_lastname AS rated_by_lastname,
    u2.user_firstname AS rated_for_firstname,
    u2.user_lastname AS rated_for_lastname,
    rs.rate_astype_lookup,
    r.rating_value,
    r.rating_comment
FROM user_ratings r
JOIN {{ ref('dim_vb_users') }} u1  -- Correct reference to users model for the rating giver
    ON r.rating_by_user_id = u1.user_id
JOIN {{ ref('dim_vb_users') }} u2  -- Correct reference to users model for the rated user
    ON r.rating_for_user_id = u2.user_id
JOIN {{ ref('dim_rating_astypes_lookup') }} rs  -- Correct reference to rate astype lookup model
    ON r.rating_astype = rs.rate_astype_lookup;

{{ config(materialized='table') }}

WITH sellers AS (
    SELECT
        item_seller_user_id AS user_id,
        'Seller' AS role,
        COUNT(item_name) AS items_sold,
        SUM(item_soldamount) AS total_revenue
    FROM {{ ref('fact_vb_items') }}
    WHERE item_sold = 'Y'
    GROUP BY item_seller_user_id
),
buyers AS (
    SELECT
        item_buyer_user_id AS user_id,
        'Buyer' AS role,
        COUNT(item_name) AS items_bought,
        SUM(item_soldamount) AS total_spent
    FROM {{ ref('fact_vb_items') }}
    WHERE item_sold = 'Y'
    GROUP BY item_buyer_user_id
)
SELECT * FROM sellers
UNION ALL
SELECT * FROM buyers

{{ config(materialized='table') }}

WITH user_ratings_summary AS (
    SELECT
        rating_for_user_id,
        COUNT(rating_id) AS total_ratings,
        AVG(rating_value) AS avg_rating,
        MAX(rating_value) AS max_rating,
        MIN(rating_value) AS min_rating
    FROM {{ ref('fact_vb_user_ratings') }}  -- Corrected reference to fact_vb_user_ratings
    GROUP BY rating_for_user_id
)

SELECT
    urs.rating_for_user_id,
    u.user_firstname AS rated_for_firstname,
    u.user_lastname AS rated_for_lastname,
    urs.total_ratings,
    urs.avg_rating,
    urs.max_rating,
    urs.min_rating
FROM user_ratings_summary urs
JOIN {{ ref('dim_vb_users') }} u  -- Corrected reference to dim_vb_users model
    ON urs.rating_for_user_id = u.user_id;

---

VBAY Data Warehouse YAML Configurations

This collection of YAML configurations represents multiple dbt YAML files merged into a single view for simplicity and clarity on my website. It defines the structure, metadata, and quality checks for the VBAY data warehouse, including models, sources, and fact and dimension tables. Each section highlights the purpose of the tables, such as user information, bids, items, and ratings, along with detailed column descriptions for easy understanding. By consolidating these YAML files, this display demonstrates best practices in data modeling, governance, and transparency for scalable analytics projects.

version: 2

models:
  - name: vb_bid_status_lookup
    description: "Lookup table for bid statuses."
    columns:
      - name: bid_status
        description: "Status of the bid."

version: 2

models:
  - name: vb_item_types_lookup
    description: "Lookup table for item types."
    columns:
      - name: item_type
        description: "Type of item."

version: 2

models:
  - name: vb_rating_astypes_lookup
    description: "Lookup table for rating types."
    columns:
      - name: rate_astype_lookup
        description: "Type of rating."


version: 2

models:
  - name: vb_users
    description: "Table containing information about VBAY users."
    columns:
      - name: user_id
        description: "Unique identifier for each user."
      - name: user_firstname
        description: "User's first name."
      - name: user_lastname
        description: "User's last name."
      - name: user_email
        description: "User's email address."
      - name: user_zip_code
        description: "User's zip code."

version: 2

models:
  - name: vb_zip_codes
    description: "Table containing information about zip codes."
    columns:
      - name: zip_code
        description: "Zip code."
      - name: zip_city
        description: "City associated with the zip code."
      - name: zip_state
        description: "State associated with the zip code."
      - name: zip_lat
        description: "Latitude of the zip code."
      - name: zip_lng
        description: "Longitude of the zip code."

version: 2

models:
  - name: vb_bids
    description: "Contains transformed bid data with proper timestamp for bid_datetime"
    columns:
      - name: bid_id
        description: "Unique identifier for each bid"
      - name: bid_user_id
        description: "User ID of the person making the bid"
      - name: bid_item_id
        description: "ID of the item being bid on"
      - name: bid_datetime
        description: "Timestamp of the bid, converted from Unix timestamp"
      - name: bid_amount
        description: "Amount of the bid"
      - name: bid_status
        description: "Status of the bid"

version: 2

models:
  - name: vb_items
    description: "Contains transformed item data with proper timestamp for item_enddate"
    columns:
      - name: item_name
        description: "Name of the item"
      - name: item_type
        description: "Type/category of the item"
      - name: item_description
        description: "Description of the item"
      - name: item_reserve
        description: "Reserve price for the item"
      - name: item_enddate
        description: "End date of the item (converted from Unix timestamp)"
      - name: item_sold
        description: "Indicates whether the item has been sold (Y/N)"
      - name: item_seller_user_id
        description: "User ID of the seller"
      - name: item_buyer_user_id
        description: "User ID of the buyer"
      - name: item_soldamount
        description: "Amount the item was sold for"

version: 2

models:
  - name: vb_user_ratings
    description: "Table containing ratings given by VBAY users."
    columns:
      - name: rating_id
        description: "Unique identifier for each rating."
      - name: rating_by_user_id
        description: "User ID of the user giving the rating."
      - name: rating_for_user_id
        description: "User ID of the user receiving the rating."
      - name: rating_astype
        description: "Type of rating given."
      - name: rating_value
        description: "Value of the rating."
      - name: rating_comment
        description: "Comment associated with the rating."

version: 2

models:
  - name: dim_vb_users
    description: "Dimension table for user information"
  - name: dim_vb_item_types_lookup
    description: "Lookup table for item types"
  - name: dim_vb_bid_status_lookup
    description: "Lookup table for bid status values"
  - name: dim_vb_rating_astypes_lookup
    description: "Lookup table for rating types"
  - name: dim_vb_zip_codes
    description: "Dimension table for zip code metadata"
  - name: fact_vb_items
    description: "Fact table for VBAY items"
  - name: fact_vb_user_ratings
    description: "Fact table for VBAY user ratings"
  - name: fact_vb_bids
    description: "Fact table for VBAY bids"
  - name: fact_user_ratings_summary
    description: "Aggregated user ratings summary"
  - name: fact_top_users
    description: "Aggregated table for top buyers and sellers"

version: 2

sources:
  - name: raw
    schema: vbay
    tables:
      - name: vb_users
      - name: vb_item_types_lookup
      - name: vb_bid_status_lookup
      - name: vb_rating_astypes_lookup
      - name: vb_zip_codes
      - name: vb_items
      - name: vb_user_ratings
      - name: vb_bids

---

Database Initialization and Table Setup Script

This SQL script initializes a new database named moze2 and sets up metadata tables and constraints for a relational database. It starts by creating the database if it doesn’t already exist, then drops existing tables and constraints to prepare for a fresh setup. The script defines a state_lookup table for storing state codes, and a customers table with attributes such as customer email, city, and state. Constraints ensure data integrity, including primary keys, unique email addresses, and valid price ranges. Additionally, a foreign key links customer_state to state_lookup.

if not exists(select * from sys.databases where name='moze2')
create database moze2
GO
use moze2
GO
-- down data
if exists(select * from information_schema.TABLE_CONSTRAINTS
where constraint_name='fk_jobs_job_contracted_by')
alter table jobs drop constraint fk_jobs_job_contracted_by
if exists(select * from information_schema.TABLE_CONSTRAINTS
where constraint_name='fk_jobs_job_submitted_by')
alter table jobs drop constraint fk_jobs_job_submitted_by
drop table if exists jobs
if exists(select * from information_schema.TABLE_CONSTRAINTS
where constraint_name='fk_contractors_contractor_state')
alter table contractors drop constraint
fk_contractors_contractor_state
drop table if exists contractors
if exists(select * from information_schema.TABLE_CONSTRAINTS
where constraint_name='fk_customers_customer_state')
alter table customers drop constraint fk_customers_customer_state
drop table if exists customers
drop table if exists state_lookup
GO
-- up metadata
create table state_lookup(
state_code char(2) not null,
constraint pk_state_lookup_state_code primary key(state_code)
)
GO
create table customers (
customer_id int identity not null,
customer_email varchar(50) not null,
customer_min_price money not null,
customer_max_price money not null,
customer_city varchar(50) not null,
customer_state char(2) not null,
constraint pk_customers_customer_id primary key (customer_id),
constraint u_customer_email unique (customer_email),
constraint ck_min_max_price check (customer_min_price <=
customer_max_price)
)
alter table customers
add constraint fk_customers_customer_state foreign key
(customer_state)
references state_lookup(state_code)

Comprehensive SQL Queries for an E-commerce Database

This SQL script contains a series of queries designed to extract, join, and analyze data from a hypothetical e-commerce database. The database consists of tables like vb_items, vb_users, vb_bids, and vb_zip_codes. The queries cover a wide range of operations, such as filtering items by type and price, calculating item margins, identifying users by location, categorizing items by price range, and fetching user ratings and bid history. Complex joins between users, items, bids, and zip codes provide insights into buyer and seller activities, user ratings, and the geographical distribution of transactions. The script also highlights inactive users and uncategorized items, facilitating data-driven decisions for business optimization.

select item_name, item_type, item_reserve, item_soldamount
from vb_items
where item_type='Collectables'
order by item_name
select * from vb_items
select * from vb_users
select s.user_email, s.user_firstname, s.user_lastname, i.item_type,
i.item_name
from vb_items i
join vb_users s on i.item_seller_user_id = s.user_id
where i.item_type = 'Antiques'
select * from vb_items
select * from vb_users
select s.user_email as sellers_email, b.user_email as buyers_email,
i.item_soldamount - i.item_reserve as item_margin, i.*
from vb_items i
join vb_users s on s.user_id = i.item_seller_user_id
join vb_users b on b.user_id = i.item_buyer_user_id
where i.item_sold = 1
order by item_margin desc
select * from vb_users
where user_zip_code like '13%'
select * from vb_zip_codes
select s.user_firstname+' '+s.user_lastname as user_name, s.user_email,
z.zip_city, z.zip_state, z.zip_code
from vb_users s
join vb_zip_codes z on s.user_zip_code = z.zip_code
where z.zip_state = 'NY'
order by z.zip_city, s.user_lastname, s.user_firstname
select item_id, item_name, item_type, item_reserve
from vb_items
where item_sold='false' and item_reserve > 250
order by item_reserve desc
select item_id, item_name, item_type, item_reserve,
case
when item_reserve >= 250 then 'high-priced'
when item_reserve <= 50 then 'low-priced'
ELSE 'average-priced'
end as category
from vb_items
where item_type != 'All Other'
select b.bid_id, u.user_firstname+' '+u.user_lastname as user_name,
u.user_email, b.bid_datetime, b.bid_amount
from vb_bids b
join vb_users u on b.bid_user_id = u.user_id
order by b.bid_datetime desc
select * from vb_items
select b.bid_user_id, u.user_lastname+', '+u.user_firstname as user_name,
u.user_email, b.bid_datetime, i.item_id, i.item_name, b.bid_amount,
b.bid_status
from vb_bids b
join vb_users u on b.bid_user_id = u.user_id
join vb_items i on b.bid_item_id=i.item_id
where b.bid_status != 'ok'
order by user_name, b.bid_datetime
select i.item_id, i.item_name, i.item_type, u.user_firstname+'
'+u.user_lastname as user_name, i.item_reserve
from vb_items i
join vb_users u on i.item_seller_user_id=u.user_id
select u.user_firstname+' '+u.user_lastname as user_name,
r.rating_for_user_id, r.rating_value, r.rating_comment
from vb_user_ratings r
join vb_users u on r.rating_by_user_id = u.user_id
where r.rating_astype='Seller'
select
i. item_id, i.item_name, i.item_type, i.item_soldamount,
case
when r.rating_astype = 'Buyer' then u.user_firstname+'
'+u.user_lastname
else 'NA'
end as buyer_name,
case
when r.rating_astype = 'Seller' then u.user_firstname+'
'+u.user_lastname
else 'NA'
end as seller_name,
case
when r.rating_astype = 'Buyer' then z.zip_city
else 'NA'
end as buyer_city,
case
when r.rating_astype = 'Seller' then z.zip_city
else 'NA'
end as seller_city,
case
when r.rating_astype = 'Buyer' then z.zip_state
else 'NA'
end as buyer_state,
case
when r.rating_astype = 'Seller' then z.zip_state
else 'NA'
end as seller_state
from vb_users u
join vb_bids b on b.bid_user_id = u.user_id
join vb_items i ON i.item_buyer_user_id = u.user_id
join vb_user_ratings r ON r.rating_for_user_id = u.user_id
join vb_zip_codes z ON z.zip_code = u.user_zip_code;
select distinct user_firstname+' '+user_lastname as user_name, user_email
from vb_users u
left join vb_bids b on u.user_id = b.bid_user_id
left join vb_items i on u.user_id = i.item_seller_user_id
where b.bid_user_id is null or i.item_seller_user_id is null or
i.item_buyer_user_id is null

Comprehensive SQL Queries for Online Auction Insights

This collection of SQL queries provides in-depth insights into an online auction platform’s operations and user behavior. It covers various aspects, including item bids, user activity, seller ratings, and performance analysis. Key features include aggregated metrics like minimum and maximum bid amounts for items, user engagement levels based on bidding activity, and comparative analytics of item types such as Antiques and Collectibles. Advanced queries leverage window functions to track bidding histories, identify trends, and compute metrics like bid-to-item ratios. The dataset also highlights top-performing sellers by comparing individual ratings against the platform’s overall average, facilitating a detailed understanding of user engagement and auction dynamics.

select item_name, item_reserve, min(bid_amount) as min_bid, max(bid_amount) as
max_bid, item_soldamount
from vb_items
join vb_bids on item_id = bid_item_id
where bid_status = 'ok'
group by item_name, item_reserve, item_soldamount
order by item_reserve desc
with user_bids as (
select s.user_email, s.user_firstname, s.user_lastname, count(*) as bid_counts,
case
when count(*) between 0 and 1 then 'Low'
when count(*) between 2 and 4 then 'Moderate'
else 'High'
end as user_bid_activity
from vb_users s
left join vb_bids b
on b.bid_user_id = s.user_id
where b.bid_status = 'ok'
group by s.user_email, s.user_firstname, s.user_lastname
)
select user_bid_activity, count(*) as user_count
from user_bids
group by user_bid_activity
order by user_count
select item_name, item_type, item_reserve, item_soldamount
from vb_items
where item_type = 'Collectables'
order by item_name
--1
select item_type, item_reserve, count(*) as item_count, min(item_reserve) as
min_item_reserve, avg(item_reserve) as avg_item_reserve, max(item_reserve) as
max_item_reserve
from vb_items
group by item_type
order by item_type
--2
select
item_name, item_type, item_reserve,
(select min(item_reserve) from vb_items where item_type = 'Antiques') as
min_reserve_antiques,
(select avg(item_reserve) from vb_items where item_type = 'Antiques') as
avg_reserve_antiques,
(select max(item_reserve) from vb_items where item_type = 'Antiques') as
max_reserve_antiques,
(select min(item_reserve) from vb_items where item_type = 'Collectables') as
min_reserve_collectables,
(select avg(item_reserve) from vb_items where item_type = 'Collectables') as
avg_reserve_collectables,
(select max(item_reserve) from vb_items where item_type = 'Collectables') as
max_reserve_collectables
from vb_items
where item_type in ('Antiques', 'Collectables')
--3
select concat(u.user_firstname, ' ', u.user_lastname) as user_name,
avg(cast(r.rating_value as decimal(3,2))) as avg_seller_rating,
count(r.rating_value) as number_of_ratings
from vb_user_ratings r
join vb_users u on r.rating_for_user_id=u.user_id
group by u.user_lastname, u.user_firstname
--4
select i.item_name, count(b.bid_item_id) as number_of_bids
from vb_items i
join vb_bids b on i.item_id = b.bid_item_id
where i.item_type = 'Collectables'
group by i.item_name
having count(b.bid_item_id)>1
order by number_of_bids desc
--5
with bid_history as (
select b.bid_datetime, concat(u.user_firstname,' ',u.user_lastname) as user_name,
i.item_id, i.item_name, b.bid_amount,
lag(b.bid_amount) over (order by b.bid_datetime) as previous_bid
from vb_bids b
join vb_items i on b.bid_item_id=i.item_id
join vb_users u on b.bid_user_id=u.user_id
where i.item_id=11
)
select * from bid_history
where bid_amount > previous_bid
--6
with bid_history as (
select
b.bid_datetime,
CONCAT(u.user_firstname, ' ', u.user_lastname) AS user_name,
i.item_id,
i.item_name,
b.bid_amount,
lag(b.bid_amount) over (order by b.bid_datetime) as previous_bid,
lag(CONCAT(u.user_firstname, ' ', u.user_lastname)) over (order by
b.bid_datetime) as previous_bidder,
lead(CONCAT(u.user_firstname, ' ', u.user_lastname)) over (order by
b.bid_datetime) as next_bidder
from
vb_bids b
join vb_items i on b.bid_item_id = i.item_id
join vb_users u on b.bid_user_id = u.user_id
where
i.item_id = 11
)
select * from bid_history
where bid_amount > previous_bid
--7
select concat(user_firstname,' ',user_lastname) as user_name, user_email
from vb_users u
join vb_user_ratings r on u.user_id=r.rating_by_user_id
group by u.user_id, u.user_firstname, u.user_lastname, u.user_email
having
count(r.rating_by_user_id)>1
and
avg(r.rating_value) > (
select avg(rating_value) from vb_user_ratings
)
--8
with userbids as (
select
u.user_id,
CONCAT(u.user_firstname, ' ', u.user_lastname) AS user_name,
u.user_email,
count(DISTINCT b.bid_id) as total_bids,
count(DISTINCT b.bid_item_id) as total_items_bid_upon
from vb_users u
left join vb_bids b ON u.user_id = b.bid_user_id
group by u.user_id, u.user_firstname, u.user_lastname, u.user_email
)
select
user_name, user_email, total_bids, total_items_bid_upon,
case
when total_items_bid_upon > 0 then CAST(total_bids as decimal) /
total_items_bid_upon
else 0
end as bid_to_item_ratio
from userbids
order by bid_to_item_ratio desc
--9
with validbids as (
select
b.bid_item_id,
b.bid_amount,
CONCAT(u.user_firstname,' ',u.user_lastname) as highest_bidder_name,
ROW_NUMBER() over (partition by b.bid_item_id order by b.bid_amount desc)
as bid_rank
from vb_bids b
join vb_users u on b.bid_user_id = u.user_id
join vb_items i on b.bid_item_id = i.item_id
where i.item_sold = 'false'
)
select i.item_id, i.item_name, vb.highest_bidder_name, vb.bid_amount as highest_bid
from vb_items i
left join validbids vb on i.item_id = vb.bid_item_id and vb.bid_rank = 1
order by i.item_id
--10
with UserRatings as (
select u.user_id, CONCAT(u.user_firstname, ' ', u.user_lastname) as user_name,
count(r.rating_value) as number_of_ratings, cast(avg(r.rating_value) AS DECIMAL(8,
2)) AS avg_seller_rating
from vb_user_ratings r
join vb_users u on r.rating_for_user_id = u.user_id
group by u.user_id, u.user_firstname, u.user_lastname
),
OverallAverage as (
select avg(rating_value) as overall_avg_seller_rating
from vb_user_ratings
)
select ur.user_name, ur.number_of_ratings, ur.avg_seller_rating,
oa.overall_avg_seller_rating, ur.avg_seller_rating - oa.overall_avg_seller_rating
AS rating_difference
from UserRatings ur
cross join OverallAverage oa

SQL Script for Managing Students and Majors with Advanced Functions and Triggers

This SQL script provides a comprehensive solution for managing a database of students and majors. It includes a procedure to upsert major records, a custom string concatenation function, and a view for displaying detailed student information. Additionally, it introduces a keyword-based search function for major names, adds columns to track student activity status, and implements a trigger to automate updates to the student_active field. The script ensures efficient data handling, integrity, and automation for academic database systems.

--1a
select *
from majors
if OBJECT_ID('dbo.p_upsert_major', 'P') is not null
begin
drop procedure dbo.p_upsert_major
end
create procedure p_upsert_major
@parameter_majorcode varchar(50),
@parameter_majorname varchar(50)
as
begin
set nocount on
declare @v_major_id int
select @v_major_id = major_id
from majors
where major_code = @parameter_majorcode
if @v_major_id is not null
begin
update majors
set major_name = @parameter_majorname
where major_code = @parameter_majorcode
end
else
begin
-- Insert a new major
insert into majors (major_id, major_code, major_name)
select isnull(max(major_id), 0) + 1, @parameter_majorcode,
@parameter_majorname
from majors
end
end
exec p_upsert_major @parameter_majorcode = 'CSC', @parameter_majorname =
'Computer Science';
exec p_upsert_major @parameter_majorcode = 'FIN', @parameter_majorname =
'Finance';
select * from majors
--2a
if object_id('dbo.f_concat', 'fn') is not null
begin
drop function dbo.f_concat
end
create function dbo.f_concat (
@a varchar(50),
@b varchar(50),
@sep char(1)
)
returns varchar(101)
as
begin
declare @resultconcat varchar(101)
set @resultconcat = @a + @sep + @b
return @resultconcat
end
--2b
create view v_students as
select
s.student_id, dbo.f_concat(s.student_firstname, s.student_lastname, ',
') AS student_name_last_first, dbo.f_concat(s.student_lastname,
s.student_firstname, ', ') AS student_name_first_last, s.student_gpa,
m.major_name
from students s
left join
majors m on s.student_major_id = m.major_id
select * from v_students
--3a
select
m.major_id, m.major_code, m.major_name, TRIM(value) AS keyword
from
majors m
cross apply STRING_SPLIT(m.major_name, ' ')
--3b
if OBJECT_ID('dbo.f_search_majors', 'TF') is not null
begin
drop function dbo.f_search_majors;
end
create function dbo.f_search_majors (
@keyword varchar(50)
)
returns @result table (
major_id int,
major_code varchar(50),
major_name varchar(50),
keyword varchar(50)
)
as
begin
insert into @result
select
m.major_id,
m.major_code,
m.major_name,
trim(value) as keyword
from majors m
cross apply string_split(m.major_name, ' ')
where trim(value) = @keyword
return
end
select * from dbo.f_search_majors('Science')
--4a
alter table students
add student_active char(1) default 'Y' not null
alter table students
add student_inactive_date date null
select * from students
--4b
if exists (select * from sys.triggers where object_id =
OBJECT_ID(N'dbo.TR_update_student_active'))
begin
drop trigger dbo.TR_update_student_active
end
create trigger TR_update_student_active
on students after insert, update
as
begin
set nocount on
update s
set s.student_active = case when i.student_inactive_date is not null
then 'N'
else 'Y' end
from students s
inner join inserted i on s.student_id = i.student_id
end
--4c
update students
set student_active = 'N',
student_inactive_date = '2020-08-01'
where student_year_name = 'Graduate'
and student_inactive_date is null
select * from students
--4d
update students
set student_active = 'Y',
student_inactive_date = null
where student_year_name = 'Graduate'
and student_active = 'N'
select * from students

SQL Procedures for Managing Majors, Bidding System, and User Ratings

This SQL script showcases advanced database management procedures, including upserting major records, placing bids, and setting up user ratings. It features robust transaction handling with error detection and rollback mechanisms to maintain data integrity. The p_upset_major procedure ensures the efficient updating or insertion of major records, while the p_place_bid procedure handles a bidding system with logic to validate bids against conditions like minimum reserve or seller restrictions. These procedures demonstrate the implementation of transactional SQL logic for reliable and scalable database operations.

use tinyu;
drop procedure if exists dbo.p_upset_major;
create procedure dbo.p_upset_major (
@major_code char(3),
@major_name varchar(50)
)
as
begin
begin try
begin transaction;
-- data logic
if exists (select 1 from majors where major_code = @major_code)
begin
update majors
set major_name = @major_name
where major_code = @major_code;
if @@rowcount <> 1
throw 50002, 'p_upset_major: update error;', 1;
end
else
begin
declare @id int = (select isnull(max(major_id), 0) + 1 from majors);
insert into majors (major_id, major_code, major_name)
values (@id, @major_code, @major_name);
if @@rowcount <> 1
throw 50002, 'p_upset_major: insert error;', 1;
end
commit;
end try
begin catch
rollback;
throw;
end catch
end;
--2
exec dbo.p_upset_major @major_code = 'CS', @major_name = 'Computer Science';
exec dbo.p_upset_major @major_code = 'MA', @major_name = 'Math';
select * from majors
--3
drop procedure if exists dbo.p_place_bid
set ansi_nulls on
go
set quoted_identifier off
go
create procedure dbo.p_place_bid
(
@bid_item_id int,
@bid_user_id int,
@bid_amount money
)
as
begin
declare @max_bid_amount money
declare @item_seller_user_id int
declare @bid_status varchar(20)
declare @bid_id int
begin transaction
begin try
-- be optimistic :-)
set @bid_status = 'ok'
set @max_bid_amount = (select max(bid_amount) from vb_bids where
bid_item_id = @bid_item_id and bid_status = 'ok')
set @item_seller_user_id = (select item_seller_user_id from vb_items where
item_id = @bid_item_id)
if @max_bid_amount is null
set @max_bid_amount = (select item_reserve from vb_items where item_id
= @bid_item_id)
if @item_seller_user_id = @bid_user_id
set @bid_status = 'item_seller'
if @bid_amount <= @max_bid_amount
set @bid_status = 'low_bid'
insert into vb_bids (bid_user_id, bid_item_id, bid_amount, bid_status)
values (@bid_user_id, @bid_item_id, @bid_amount, @bid_status)
set @bid_id = scope_identity()
commit transaction
return @bid_id
end try
begin catch
rollback transaction
declare @error_message nvarchar(4000);
declare @error_severity int;
declare @error_state int;
select
@error_message = error_message(),
@error_severity = error_severity(),
@error_state = error_state();
raiserror (@error_message, @error_severity, @error_state);
end catch
end
go
--4
declare @bid_id int;
exec @bid_id = dbo.p_place_bid @bid_item_id = 36, @bid_user_id = 2, @bid_amount =
105.00;
select * from vb_bids where bid_id = @bid_id;
--5
drop procedure if exists dvo.p_rate_user;
set ansi_nulls on

Database Redesign for Book and Author Management

This script demonstrates the redesign and refinement of a database schema for managing books and authors in a publishing system. Starting with the creation of the fudgenbooks table, which stores details about books, authors, and publishers, it includes constraints such as a primary key on isbn for data integrity. Additionally, it handles homework tasks such as dropping existing constraints (fk_book_authors_isbn and fk_book_authors_author_name) to allow for schema updates or improvements. This exercise emphasizes the importance of maintaining referential integrity while restructuring database relationships for better organization and scalability.

/*
Original Table
*/
USE demo
GO
DROP TABLE IF EXISTS fudgenbooks
GO
CREATE TABLE fudgenbooks
(
isbn VARCHAR(20) NOT NULL,
title VARCHAR(50) NOT NULL,
price MONEY,
author1 VARCHAR(20) NOT NULL,
author2 VARCHAR(20) NULL,
author3 VARCHAR(20) NULL,
subjects VARCHAR(100) NOT NULL,
pages INT NOT NULL,
pub_no INT NOT NULL,
pub_name VARCHAR(50) NOT NULL,
pub_website VARCHAR(50) NOT NULL,
CONSTRAINT pk_fudgenbooks_isbn PRIMARY KEY (isbn)
)
GO
INSERT INTO fudgenbooks VALUES
('372317842','Introduction to Money Laundering', 29.95,'Mandafort', 'Made-Off',
NULL, 'scams,money laundering',367,101,'Rypoff','http://www.rypoffpublishing.com'),
('472325845','Imbezzle Like a Pro',34.95,'Made-Off','Moneesgon',
NULL,'imbezzle,scams',670,101,'Rypoff','http://www.rypoffpublishing.com'),
('535621977','The Internet Scammer''s Bible',44.95, 'Screwm', 'Sucka', NULL,
'phising,id theft,scams',944,102, 'BS Press','http://www.bspress.com/books'),
('635619239','Art of the Ponzi Scheme', 39.95,
'Dewey','Screwm','Howe','scams,ponzi',450,102,'BS
Press','http://www.bspress.com/books')
GO
SELECT * FROM fudgenbooks
GO
/*
Homework 10
*/
-- DROP CONSTRAINTS
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE CONSTRAINT_NAME = 'fk_book_authors_isbn')
ALTER TABLE fb_book_authors DROP fk_book_authors_isbn;
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE CONSTRAINT_NAME = 'fk_book_authors_author_name')
ALTER TABLE fb_book_authors DROP fk_book_authors_author_name;
IF EXISTS (
SELECT *