SQL

Here is SQL test I took during a job interview. First is the description, or you can skip directly to the answer. Description: A social network under development needs a query that returns all profiles and the types of their relationships with each other. The result should have the following columns: profile | %related_profile_1% .. %related_profile_N% - type of relation of specific profiles: The column name is the related profile username. Columns are sorted in ascending order by name. The result should be sorted in ascending order by profile. ...

Here is SQL test I took during a job interview. First is the description, or you can skip directly to the answer. Description: As part of developing the call history functionality of a dialer application, create a query that returns a list of all contacts, the total duration of their calls, and the number of calls during the work week and weekends. The result should have the following column: full_name | phone | type | workweek | weekend. ...

I took this SQL test during a job interview. The instructions are (spelling mistake is theirs): “We’d like a data model for quick lookup per user. Create a query that returns 1 row per user containing information on their first purchance and their last.” We have a table that looks like this - the table is called subscriptions which can also be considered transactions. Table "public.subscriptions" Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+--------- transaction_id | character(36) | | not null | purchased_at | timestamp without time zone | | not null | purchase_price | double precision | | not null | user_id | character varying(255) | | not null | The way I did this was with two sub queries. One sub query ranked each user’s transactions. The other sub query counted total transactions per user - use this number to know which transaction was their last. The query with total transactions per user has one row for every user, so in the main query start with that, then join to the ranking query twice, once for the first transaction and once for the last. ...

Here’s how to convert a numeric integer field of seconds to HH:MM:SS. Use the INTERVAL PostgreSQL data type. The INTERVAL data type is used to store and manipulate a time period. Example SELECT 120 * interval '1 sec'; Output |120 seconds to HH:MM:SS| |-----------------------| |00:02:00| An example with a table of rows of call records that have the length of call in seconds in a column called duration. select duration , duration * interval '1 sec' seconds_to_hh_mm_ss , duration * interval '1 min' min_to_hh_mm_ss from calls; Output ...