Beta

ask

Given the database where all the users’ data is stored in one huge XML string, fetch it as separate rows and columns.

Notes

  • The private field determines whether the user's email address should be publicly visible
  • If the profile is private, email_address should equal "Hidden"
  • The users may have multiple email addresses
  • If no email addresses are provided, email_address should equal "None"
  • If there’re multiple email addresses, the first one should be shown
  • The date_of_birth is in the yyyy-mm-dd format
  • The age fields represents the user's age in years
  • Order the result by the first_name, and last_name columns

Input table

-------------------------
| Table | Column | Type |
|-------+--------+------|
| users | id | int |
| | data | xml |
-------------------------

XML format

<data>
<user>
<first_name>...</first_name>
<last_name>...</last_name>
<date_of_birth>...</date_of_birth>
<private>...</private>
<email_addresses>
<address>...</address>
<address>...</address>
...
<address>...</address>
</email_addresses>
</user>
<user>...</user>
...
<user>...</user>
</data>

Output table

------------------------
| Column | Type |
|---------------+------|
| first_name | text |
| last_name | text |
| age | int |
| email_address | text |
------------------------

Solution:

select
(xpath('/user/first_name/text()', data))[1]::text as first_name,
(xpath('/user/last_name/text()', data))[1]::text as last_name,
date_part('year', age(((xpath('/user/date_of_birth/text()', data))[1]::text)::date))::int as age,
case
when (xpath('/user/private/text()', data))[1]::text = 'true' then 'Hidden'
when not xpath_exists('/user/email_addresses/address', data) then 'None'
else (xpath('/user/email_addresses/address/text()', data))[1]
end as email_address
from unnest(xpath('/data/user', (select data from users limit 1))) as data
order by first_name, last_name;

Link

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

How to connect to SharePoint Online using Powershell

Why Start a Developer Community?

developer community

The bitwise complement, or not operator, is unsafe

Hammerhead Karoo Product Development Series — Chapters 1 & 2 of 10

FluentU Vs. Memrise Best Review — Pick The #1 Learning App

AIOps — the Basics

Oraichain x CosmWasm: Building open-source IDE for developers of Cosmos-SDK chains

Can Kafka be used for video streaming?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Embedded SQL and Dynamic SQL

SQL Introduction 1

An internal hard drive

Ease into SQL

Embedded SQL and Dynamic SQL a