Sales Search and Analysis

Hi there,

I hope you’re doing good!

I’m Jacques and I’m taking the ‘SQL for Data Analyitcs’ course !

I’m struggling again with the ‘Sales Search and Analysis’ part, especially with the JSON and JSONB functions. Has someone already done it?

I’m trying to create a searchable materialized table but I cannot create a specifical column to have the customer_id. I tried to use function "JSONB_ARRAY_ELEMENTS to kinda put away an element as a column, but I don’t really know how to do it properly… Can someone give me a hand? Here is my code below:

    create materialized view searchable as (
        select cjsonb_array_elements(customer_json -> 'customer_id') as customer_id 
        , customer_json
        , to_tsvector('english', customer_json::text) as search_vector
        from customer_sales
        limit 20;
    );

Thank you very much for your help!

Cheers

Jacques

Hey Jacques,

I’m doing great! Hope you’re doing well also :slightly_smiling_face:

There are a few things causing your code to not pass the check:

  1. You do not need to use cjsonb_array_elements to get the customer_id from customer_json . You can simply use customer_json -> 'customer_id' AS customer_id. You can read more about this in the JSONB: Pre-Parsed JSON step of the Analytics Using Complex Data Types lesson.
  2. You will need to name your materialized view customer_search, as specified in the task instructions.
Here is the solution to the task:
CREATE MATERIALIZED VIEW customer_search AS (
    SELECT
        customer_json -> 'customer_id' AS customer_id,
        customer_json,
        TO_TSVECTOR('english', customer_json::text) AS search_vector
    FROM
        customer_sales
);

Let me know if you have any questions!

Best,
Lorraine

Hi Lorraine !

Thanks so much for getting me back again !
I got it, thanks ! :slight_smile:

I need another hand tho about the 4st question, that ask to create a cross join of the table products to know how commun is it for someone to buy a scooter and an automobile.

This is my code:

select distinct(p1.*), p2.*
from products p1 cross join products p2 
where p1.model <> p2.model 
  and (p1.product_type = 'scooter' and p2.product_type = 'automobile')
  and (p1.model not like '%Limited Edition%' and p2.model not like '%Limited Edition%');

I didn’t know if I had to group all together the Limited Edition with theirs respectives “roots” (for example, ‘Bat Limited Edition’ and ‘Bat’) or just removed then. In the first case, I’d create a new colomn that I can just keep, but I don’t know if it’s the best choice.

Also, I tried something else at the beginning:"select distinct(p1.*, p2.*) to have one and only one distinct combinaisons.

Thank you so much by advance of your help again :slight_smile:

Best,

Jacques

Hey Jacques,

Your answer is close! Yes, the limited edition releases should be considered the same as their standard model counterpart. So, the pair “Bat” and “Model Chi” is considered the same as the pair “Bat Limited Edition” and “Model Chi”. Below is the solution!

Solution
SELECT DISTINCT
    p1.model,
    p2.model
FROM
    products p1
    CROSS JOIN products p2
WHERE
    p1.product_type = 'scooter'
    AND p2.product_type = 'automobile'
    AND p1.model NOT ILIKE '%Limited Edition%'

Let me know if you have any more questions :slight_smile:

Best,
Lorraine