**کوئری‌های شما باید روی _MySQL_ قابل اجرا باشند.** برای چک کردن سینتکس کد *SQL* خود می‌توانید از **[این وبسایت](https://www.eversql.com/sql-syntax-check-validator/)** کمک بگیرید.
در این سوال بخشی از پایگاه‌داده یک پیام‌رسان در اختیار شما قرار داده شده است، این بخش از پایگاه‌داده شامل اطلاعات حدود ۱۰۰۰ کاربر و ۲۵۰۰ گروه می‌باشد.
# جزئیات پایگاه‌داده
داده‌های اولیه برای **تست نهایی** را از [این لینک](/problemset/assignments/4367/download_problem_initial_project/234289/) دانلود کنید.
<details class="yellow">
<summary>
**توضیحات در مورد داده‌های اولیه**
</summary>
در فایل *Messenger.zip* فایلی به اسم initial.sql وجود دارد.
با اجرای این فایل، همه جداول و سطرهایی که برای **تست نهایی** مورد استفاده قرار می‌گیرد در پایگاه‌داده‌ای به `Messenger`، ایجاد می‌شوند.
</details>
<details class="grey">
<summary>
**توضیحات جداول پایگاه‌داده**
</summary>
ساختار جداول به‌شرح زیر است:
۱.**جدول `users`**: از این جدول برای نگه‌داری اطلاعات کاربران استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`INT`|شناسه کاربر (کلید اصلی)|
|`phone`|`VARCHAR(20)`|شماره موبایل کاربر (یکتا)|
|`email`|`VARCHAR(256)`|ایمیل کاربر (یکتا)|
|`name`|`VARCHAR(128)`|نام و نام‌خانوادگی کاربر|
|`password`|`VARCHAR(128)`|رمز عبور کاربر (هَش شده)|
|`reported_num`|`int`|تعداد دفعاتی که کاربر گزارش (*report*) شده|
|`created_at`|`datetime`|تاریخ عضویت|
|`updated_at`|`datetime`|تاریخ به‌روز رسانی حساب|
|`is_suspend`|`VARCHAR(1)`|آیا کاربر تعلیق شده‌است (مقدار یک) یا خیر (مقدار صفر)|
۲.**جدول `block_list`**: از این جدول برای نگه‌داری اطلاعات افراد بلاک (مسدود) شده استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون| نوع| تعریف|
| --------- | ------------ | ------------ |
| `id`| `INT`| شناسه‌ی جدول (کلید اصلی) |
| `user_id` | `INT`| شناسه کاربر بلاک کننده|
| `blocked_user_id` | `INT`| شناسه کاربر بلاک شده|
۳.**جدول `group_conversations`**: از این جدول برای نگه‌داری اطلاعات گروه‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`INT`|شناسه گروه (کلید اصلی)|
|`string_id`|`VARCHAR(20)`|آدرس (*url*) گروه (یکتا)|
|`onwer_id`|`INT`|شناسه کاربر مالک گروه|
|`name`|`VARCHAR(50)`|نام گروه|
۴.**جدول `group_conversation_users`**: از این جدول برای نگه‌داری اطلاعات کاربران هر گروه استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
|نام ستون|نوع|تعریف|
|:-------|:--|----:|
|`id`|`INT`|شناسه جدول (کلید اصلی)|
|`group_id`|`INT`|شناسه گروه|
| `user_id` | `INT`| شناسه کاربر حاضر در گروه|
|`is_admin`|`VARCHAR(1)`|آیا کاربر ادمین گروه هست (مقدار ۱) یا خیر (مقدار ۰)|
</details>
# مطلوبات🔗
کوئری‌هایی بنویسید که مطلوبات خواسته شده در هر قسمت را انجام دهد:
۱. همان‌طور که در توضیحات جداول گفته شده در جدول کاربران (`users`) ستونی به نام `is_suspend` وجود دارد که نشان می‌دهد یک کاربر تعلیق شده است یا خیر، اگر به این ستون پایگاه‌داده دقت کنید، متوجه می‌شوید که این مقدار برای تمامی رکورد‌ها **صفر** است. حال شما باید کوئری بنویسید که این ستون را برای کاربرانی که ۵ بار یا بیشتر گزارش (ستون `reported_num`) شده‌اند، برابر **یک** قرار دهد.
۲. لیستی از کاربران و تعداد بلاک شدن آن‌ها توسط کاربران دیگر.
</details>
<details class="blue">
<summary>
*توضیحات مورد مطلوب دوم*
</summary>
نام هر کاربر را در ستونی به نام `blocked_user_name` و تعداد کاربرانی که این کاربر را بلاک کرده اند را در ستونی با نام `block_count` نمایش دهید. همچنین توجه داشته باشید خروجی شما باید بر اساس تعداد بلاک‌ها (ستون `block_count`) به صورت **نزولی** مرتب شود و در صورتی که این مقدار برابر بود بر اساس نام کاربر به صورت **صعودی** مرتب شود.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| blocked_user_name | block_count |
| :---: | :---: |
| Sandra Garcia | 8 |
| Bryan Waters | 7 |
| Danielle Henry | 7 |
</details>
۳. لیستی از کاربران و کسانی که با آن‌ها در گروهی مشترک هستند و نام آن گروه.
</details>
<details class="blue">
<summary>
*توضیحات مورد مطلوب سوم*
</summary>
نام کاربر اول را در ستونی به نام `first_user` و نام کاربر دوم را در ستونی با نام `second_user` و نام گروهی که کاربران در آن مشترک هستند را در ستونی با نام `mutual_group` قرار دهید و به ترتیب ابتدا کابر اول سپس کاربر دوم و در نهایت براساس نام گروه به صورت **صعودی** مرتب شود.
توجه داشته باشید شناسه کاربر اول باید همیشه از کاربر دوم بیشتر باشد و همچنین اجرای این کوئری ممکن است کمی زمان‌گیر باشد.
3 سطر اول خروجی شما باید به شکل زیر باشد.
| first_user | second_user |mutual_group|
| :---: | :---: |:---:|
|Aaron Foster| Anna Little | Thompson, Sullivan and Turner |
|Aaron Foster | Ashley Scott | Anderson-Hill |
|Aaron Foster | Benjamin Porter | Carrillo-Parker |
</details>
۴. پس از اعمال کوئری اول ستون `is_suspend` دارای مقادیر معنی دار است، حال ساختاری را بر روی پایگاه‌داده خود پیاده کنید که هنگامی که تعداد گزارش شدن یک کاربر (ستون `reported_num`) به ۵ می‌رسد (به‌روزرسانی می‌شود) کاربر تعلیق شود (مقدار ستون `is_suspend` **یک** شود) و بر عکس، اگر کاربری تعداد گزارش شدنش کم‌تر از ۵ شد از تعلیق در بیاید (مقدار ستون `is_suspend` **صفر** شود).
<details class="red">
<summary>
توجه
</summary>
به روش ارسال پاسخ قسمت چهارم توجه داشته باشید. همچنین توجه داشته باشید برای دریافت امتیاز این قسمت باید امتیاز قسمت اول (مطلوب اول همین سوال) را دریافت کرده باشید.
</details>
# **روش ارسال پاسخ**
در یک فایل با نام `code.sql` کد خود را قرار دهید و آن را فشرده (`zip` ) کنید و در سایت بارگذاری نمایید. کد شما باید به صورت زیر باشد. (نام فایل zip مهم نیست).
```sql
-- Section1
your first query here
-- Section2
your second query here
-- Section3
your third query here
-- Section4
Delimiter $$
your fourth query here
$$
```