کوئری‌های شما باید روی آخرین نسخه‌ی MySQL قابل اجرا باشند.
محمد به‌تازگی به تیم مهندسی دیجی‌کال‍ا پیوسته. او در یکی از تسک‌هایش لازم دارد تا کوئری‌های مختلفی برای کار با داده‌هایی که با آن‌ها سروکار دارد بنویسد. داده‌هایی که محمد با آن‌ها سروکار دارد مربوط به محصولات و سفارش‌های مربوط به آن‌ها است. او یک denormalization روی داده‌ها اعمال کرده، به این‌صورت که علاوه بر اطلاعات سفارش‌های محصولات، مجموع فروش هر محصول نیز در جدول اطلاعات محصولات وجود دارد.
از آن‌جایی که محمد فرصت نوشتن کوئری‌های مدنظرش را ندارد، از شما می‌خواهیم تا این کوئری‌ها را برایش بنویسید.
جزئیات پروژه
داده‌های اولیه برای تست نمونه را از این لینک دانلود کنید.
ساختار جداول به‌شرح زیر است:
جدول products: از این جدول برای نگه‌داری اطلاعات محصولات استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
BIGINT(20) |
شناسه‌ی محصول |
name |
VARCHAR(255) |
نام محصول |
description |
TEXT |
توضیحات محصول |
price |
DECIMAL(15, 2) |
قیمت محصول |
total_profit |
DECIMAL(15, 2) |
مجموع مبلغ فروش محصول |
created_at |
TIMESTAMP |
زمان درج محصول |
جدول orders: از این جدول برای نگه‌داری لیست سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
BIGINT(20) |
شناسه‌ی سفارش |
user_id |
BIGINT(20) |
شناسه‌ی کاربر سفارش‌دهنده |
created_at |
TIMESTAMP |
زمان ایجاد سفارش |
جدول order_details: از این جدول برای نگه‌داری اطلاعات اقلام موجود در سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
BIGINT(20) |
شناسه‌ی اطلاعات محصول در سفارش |
order_id |
BIGINT(20) |
شناسه‌ی سفارش |
product_id |
BIGINT(20) |
شناسه‌ی محصول |
quantity |
INT(11) |
تعداد سفارش‌داده‌شده‌ی محصول |
جدول delivery_centers: از این جدول برای نگه‌داری اطلاعات مراکز ارسال استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
id |
BIGINT(20) |
شناسه‌ی مرکز ارسال |
name |
VARCHAR(255) |
نام مرکز ارسال |
جدول deliveries: از این جدول برای نگه‌داری اطلاعات تحویل سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:
| نام ستون | نوع | تعریف |
|---|---|---|
order_id |
BIGINT(20) |
شناسه‌ی سفارش |
delivery_center_id |
BIGINT(20) |
شناسه‌ی مرکز ارسال |
received_at |
TIMESTAMP |
زمان دریافت محصولات سفارش از انبار |
delivered_at |
TIMESTAMP |
زمان تحویل سفارش به مشتری (در صورتی که سفارش هنوز تحویل داده نشده باشد، مقدار آن برابر با NULL خواهد بود.) |
توجه داشته باشید که جدول کاربران صرفاً جهت سادگی در سؤال نیامده است. نیازی به اطلاعات چنین جدولی نیست.
مطلوبات
کوئری‌های زیر را طوری بنویسید که خروجی خواسته‌شده به‌دست آید:
- محمد اکنون متوجه شده که یک باگ در بک‌اند برنامه‌اش وجود داشته و مقدار ستون
total_profitدر جدولproductsلزوماً مطابق با اطلاعات موجود در جداولordersوorder_detailsنیست (در داده‌ها inconsistency وجود دارد). کوئری‌ای بنویسید که مقدار ستونtotal_profitجدولproductsرا طبق مقادیر موجود در جداولordersوorder_detailsبه‌روزرسانی کند تا inconsistency در صورت وجود رفع شود. - کوئری‌ای بنویسید که شناسه‌ی ۵ مرکز ارسال برتر از نظر چابک بودن را برگرداند. یک مرکز ارسال در صورتی چابک است که میانگین اختلاف زمانی بین دریافت محصول از انبار تا تحویل آن به مشتری در سفارش‌های تحویل‌داده‌شده‌اش کمتر از سایر مراکز ارسال باشد. نتایج را به‌ترتیب نزولی میزان چابک بودن مرتب کنید. تضمین می‌شود که میزان چابک بودن مراکز ارسال با یکدیگر متفاوت است. نام ستون خروجی اهمیتی ندارد.
- کوئری‌ای بنویسید که شناسه‌ی محصولاتی که تا ۷ روز پس از انتشارشان کمتر از ۱۰ فروش داشتند و حدقل ۱ فروش داشتند را به‌ترتیب صعودی شناسه‌ی محصول دریافت کند. نام ستون خروجی اهمیتی ندارد.
نکات
- کوئری‌های شما باید روی آخرین نسخه‌ی MySQL قابل اجرا باشند.
- کوئری هر بخش باید تنها شامل یک statement باشد.
- هر کوئری امتیاز جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بنویسید، کوئری‌هایی که حل کردید را بفرستید و قسمت آن کوئری را خالی بگذارید.
- در تست نمونه‌ی این سؤال، فقط کوئری بخش اول تست می‌شود.
آن‌چه باید آپلود کنید
پس از طراحی کوئری‌ها، آن‌ها را در قالب زیر در یک فایل با پسوند .sql آپلود کنید.
-- Section1
Your first query here
-- Section2
Your second query here
-- Section3
Your third query here
ارسال پاسخ برای این سؤال